CODEWITHSUNDEEP
sqlsql-server
Doctorj77
Doctorj77

Reputation: 125

How to SELECT COUNT multiple values in one column

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

Upvotes: 8

Views: 67168

Answers (4)

Dipendu Paul
Dipendu Paul

Reputation: 2753

 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
 )

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

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

Upvotes: 4

user275683
user275683

Reputation:

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 )

Upvotes: 18

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 12

Related Questions