SQL COUNT issue

Question

SQL COUNT issue

TJ_T

Old Hand

Points: 352
More actions
October 16, 2019 at 10:32 am

#3689209

I could use some help with the following COUNT issue.
Select distinct Model_id,
COUNT(CASE WHEN Doors = 4 then 1 ELSE NULL END) [4DoorCount],
COUNT(CASE WHEN Doors = 2 then 1 ELSE NULL END) [2DoorCount]
FROM Vehicle_Inv
Results:
I would like the results all on one row, but can't figure it out
:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24381 More actions · Answer 1

You want to use SUM and not COUNT

Select distinct Model_id,
SUM(CASE WHEN Doors = 4 then 1 ELSE 0 END) [4DoorCount],
SUM(CASE WHEN Doors = 2 then 1 ELSE 0 END) [2DoorCount]
FROM Vehicle_Inv

How to post data/code on a forum to get the best help.

ktflash Default port Points: 1400 More actions · Answer 2

Without knowing that the data looks like this would work, depending on what you need you must change the sum to max

SELECT  Model_id,4DoorCount=SUM(4DoorCount),2DoorCount=SUM(2DoorCount)
FROM (
Select distinct Model_id,

COUNT(CASE WHEN Doors = 4 then 1 ELSE NULL END) [4DoorCount],

COUNT(CASE WHEN Doors = 2 then 1 ELSE NULL END) [2DoorCount]

FROM Vehicle_Inv
  )
GROUP BY Model_id

What result do you get using this?

Select distinct Model_id,

   4DoorCount= COUNT(CASE WHEN Doors = 4 then 1 ELSE NULL END)

   2DoorCount= COUNT(CASE WHEN Doors = 2 then 1 ELSE NULL END)
   
FROM Vehicle_Inv

GROUP BY Model_id

This reply was modified 5 years, 1 month ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

TJ_T Old Hand Points: 352 More actions · Answer 3

TJ_T

Old Hand

Points: 352

October 16, 2019 at 1:03 pm

#3689252

Many thanks ktflash - worked like charm.