How to SELECT COUNT multiple values in one column

Question

rather a newbie at SQL, so please be gentle....as I think this is a basic one.

I'm trying to write a query with multiple (13) counts, based off Column1. The 1st Count is the over-all total. And then the 12 others are filtered by Color. I can get my results by doing multiple Counts all in one query, but this gives me 13 rows of data. The goal here is to get everything on just one row. So, almost like each count would be its own column. Here is an example of the data model

Database = CARS, Table = TYPES, Column1 = LICENSE, Column2 = COLOR

SELECT COUNT (LICENSE) AS 'Total ALL Cars'
FROM CARS.TYPES WITH (NOLOCK)

SELECT COUNT (LICENSE) AS 'Total RED Cars'
FROM CARS.TYPES WITH (NOLOCK)
WHERE COLOR = 'RED'

And on & on & on for each remaining color. This works, but again, I'm trying to streamline it all into one row of data, IF possible. Thank you in advance

possible duplicate of multiple select in one sql statement

tutuDajuju
– tutuDajuju

2014-02-26 18:46:00 +00:00
Commented Feb 26, 2014 at 18:46 — tutuDajuju
– tutuDajuju, Commented Feb 26, 2014 at 18:46

user275683 · Accepted Answer · 2014-02-26 18:22:19Z

19

You simply need to include color in select statement and group by it to count cars of each color.

 SELECT Color, Count(*)
 FROM CARS.TYPES WITH(NOLOCK)
 GROUP BY Color

or

SELECT COUNT(CASE WHEN Color = 'RED' THEN 1
                  ELSE NULL
             END) AS RedCars
       ,COUNT(CASE WHEN Color = 'BLUE' THEN 1
                   ELSE NULL
              END) AS BlueCars
       ,COUNT(*) AS AllCars
    FROM CARS.TYPES WITH ( NOLOCK )

answered Feb 26, 2014 at 18:22

user275683

Sign up to request clarification or add additional context in comments.

1 Comment

karatedog Over a year ago

best solution so far, it counts values that are not known beforehand

Hart CO · Accepted Answer · 2014-02-26 18:22:14Z

12

You can do this with a conditional SUM():

SELECT SUM(CASE WHEN Color = 'Red' THEN 1 END) AS 'Total Red Cars'
      ,SUM(CASE WHEN Color = 'Blue' THEN 1 END) AS 'Total Blue Cars'
FROM CARS.TYPES

If using MySQL you can simplify further:

SELECT SUM(Color = 'Red') AS 'Total Red Cars'
      ,SUM(Color = 'Blue') AS 'Total Blue Cars'
FROM CARS.TYPES

answered Feb 26, 2014 at 18:22

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

8 Comments

Daniel Sparing Over a year ago

You can simplify this with SUM(Color='Red').

Hart CO Over a year ago

@DanielSparing Depends on database, that would work in MySQL, but not in SQL Server.

Daniel Sparing Over a year ago

Fair enough, good to know that about SQL Server then, thanks!

Martin Smith Over a year ago

WITH (NOLOCK) is SQL Server

Doctorj77 Over a year ago

Thx Goat CO. That did it. Now I do have some NULLs on certain colors, is there a way i can remove those from being displayed?

|

Martin Smith · Accepted Answer · 2014-02-26 18:30:27Z

4

Or with PIVOT

SELECT RED + BLUE + GREEN AS total,
       RED,
       BLUE,
       GREEN
FROM   CARS.TYPES PIVOT (COUNT (LICENSE) FOR COLOR IN ([RED], [BLUE], [GREEN])) P

edited Feb 26, 2014 at 18:30

answered Feb 26, 2014 at 18:25

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

1 Comment

spinjector Over a year ago

There is beautiful simplicity here.

Dipendu Paul · Accepted Answer · 2014-02-26 19:31:28Z

0

 SELECT SUM(Al) AllCount, SUM(Red) RedCount, SUM(Green) GreenCount, ...
 (  
   SELECT 1 AS Al 
   , CASE WHEN Color = 'Red' THEN 1 ELSE 0 END AS Red 
   , CASE WHEN Color = 'Green' THEN 1 ELSE 0 END AS Green
   ... 
   FROM CARS.Types
 )

answered Feb 26, 2014 at 19:31

Dipendu Paul

2,7631 gold badge26 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to SELECT COUNT multiple values in one column

4 Answers 4

1 Comment

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related