CODEWITHSUNDEEP
sqlt-sql
hank
hank

Reputation: 21

In SQL, how can I get subtotals by group?

I'm hoping someone can help me with a SQL select statement for the following problem:

I have the following data in a table:

date color
01/23/2023 RED
01/23/2023 BLUE
01/23/2023 GREEN
01/23/2023 GREEN
01/23/2023 RED
01/23/2023 BLUE
01/23/2023 GREEN
01/24/2023 BLUE
01/24/2023 GREEN
01/24/2023 BLUE
01/24/2023 RED
01/25/2023 GREEN
01/25/2023 BLUE
01/25/2023 GREEN
01/25/2023 BLUE

and I would like to produce this:

date RED BLUE GREEN
01/23/2023 2 2 3
01/24/2023 1 2 1
01/25/2023 0 2 2

I'll have the exact colors so I don't need that to be determined. Sorry but I don't have any idea of how to even to beginning writing this select statement.

Thanks.

Upvotes: 0

Views: 55

Answers (3)

Jeff Moden
Jeff Moden

Reputation: 3494

First, it would be mighty helpful if you posted your example data as "Readily Consumable Data" on future posts. Like this for this post...

--===== Create "Readily Consumable Data to help those
     -- That would help you.
   DROP TABLE IF EXISTS #TestTable;
GO
 SELECT *
   INTO #TestTable
   FROM (VALUES
         ('01/23/2023','RED')
        ,('01/23/2023','BLUE')
        ,('01/23/2023','GREEN')
        ,('01/23/2023','GREEN')
        ,('01/23/2023','RED')
        ,('01/23/2023','BLUE')
        ,('01/23/2023','GREEN')
        ,('01/24/2023','BLUE')
        ,('01/24/2023','GREEN')
        ,('01/24/2023','BLUE')
        ,('01/24/2023','RED')
        ,('01/25/2023','GREEN')
        ,('01/25/2023','BLUE')
        ,('01/25/2023','GREEN')
        ,('01/25/2023','BLUE')
        )d(date,color)
;

Giving credit where credit is certainly due, both @Learning and @Horaciux gave excellent CROSSTAB answers that do exactly what you ask.

In anticipation of what the folks that asked originally ask you for this output will likely ask of you next, you can also easily add a "DateTotal" and a "ColorTotal". CROSSTABs do make this easy. PIVOTs? No so much and I'm not going to demo that.

Here's the code to include the additional DateTotal column and ColorTotal row. I also converted the CASE statements to IIF to make things less busy. To be sure, though, IIF resolves to CASE statements behind the scenes and that means there's no performance advantage. If you hate IIF, like some do, just change them back to CASE and Bob's your uncle. :D

 SELECT  Date      = IIF(GROUPING(date) = 1, 'Total',CONVERT(CHAR(10),date,101))
        ,Red       = SUM(IIF(color = 'RED'  ,1,0))
        ,Blue      = SUM(IIF(color = 'BLUE' ,1,0))
        ,Green     = SUM(IIF(color = 'GREEN',1,0))
        ,DateTotal = COUNT(*)
   FROM #TestTable
  GROUP BY date WITH ROLLUP
  ORDER BY GROUPING(date),Date
;

That produces the following output... enter image description here

Upvotes: 0

learning
learning

Reputation: 610

You can use CASE WHEN SUM

SELECT
    edate,
    SUM (CASE WHEN color = 'RED' THEN 1 ELSE 0 END) AS RED,
    SUM (CASE WHEN color = 'BLUE' THEN 1 ELSE 0 END) AS BLUE,
    SUM (CASE WHEN color = 'GREEN' THEN 1 ELSE 0 END) AS GREEN
FROM tablename
GROUP BY edate

Upvotes: 1

Horaciux
Horaciux

Reputation: 6477

SELECT date,
       SUM(CASE WHEN color = 'RED' THEN 1 ELSE 0 END) AS RED,
       SUM(CASE WHEN color = 'BLUE' THEN 1 ELSE 0 END) AS BLUE,
       SUM(CASE WHEN color = 'GREEN' THEN 1 ELSE 0 END) AS GREEN
FROM table1
GROUP BY date
ORDER BY date;

Upvotes: 1

Related Questions