Thread: Multi count case and then a sum for each case

1. Registered User
Join Date
Aug 2012
Posts
9

Unanswered: Multi count case and then a sum for each case

I'm using count case as below, but I also want to sum the dollar amount of EACH case.

What I'm using:

SELECT `service`,
COUNT( CASE WHEN LENGTH(`pin`) = 10 THEN 1 END ) AS 10_total ,
COUNT( CASE WHEN LENGTH(`pin`) = 15 THEN 1 END ) AS 15_total,
COUNT( CASE WHEN LENGTH(`pin`) = 20 THEN 1 END ) AS 20_total
FROM `table`
WHERE `paidDateTime` > NOW() - INTERVAL 30 day
AND `status` LIKE 'paid'
GROUP BY `service`

Output:

service 10_total 15_total 20_total
ABCD 3 50 63
DEFG 22 1 98
HIJK 2 0 22
...

I can easily add the SUM(`amount`) value, but it's all inclusive, whereas I want to get a sum for each use case.

Any help would be greatly appreciated.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```SELECT `service`
,  COUNT(CASE WHEN  10 = LENGTH(`pin`) 10 THEN 1 END) AS 10_cnt
,  Sum(  CASE WHEN  10 = LENGTH(`pin`) 10 THEN 1 END) AS 10_sum
,  COUNT(CASE WHEN  15 = LENGTH(`pin`) 10 THEN 1 END) AS 15_cnt
,  Sum(  CASE WHEN  15 = LENGTH(`pin`) 10 THEN 1 END) AS 15_sum
,  COUNT(CASE WHEN  20 = LENGTH(`pin`) 10 THEN 1 END) AS 20_cnt
,  Sum(  CASE WHEN  20 = LENGTH(`pin`) 10 THEN 1 END) AS 20_sum
FROM `table`
WHERE  `paidDateTime` > NOW() - INTERVAL 30 day
AND `status` LIKE 'paid'
GROUP BY `service````
-PatP

3. Registered User
Join Date
Aug 2012
Posts
9
Hmm. This doesn't account for the `amount`. It just gives me my count twice.

If I add SUM(`amount`) to what I already have, I get the correct value; however, it's a sum of the dollar amount for all 3 cases, and not a sum of the dollar amount for EACH case.

Now:

SELECT `service`,
COUNT( CASE WHEN LENGTH(`pin`) = 10 THEN 1 END ) AS 10_total ,
COUNT( CASE WHEN LENGTH(`pin`) = 15 THEN 1 END ) AS 15_total,
COUNT( CASE WHEN LENGTH(`pin`) = 20 THEN 1 END ) AS 20_total,
SUM(`amount`)
FROM `table`
WHERE `paidDateTime` > NOW() - INTERVAL 30 day
AND `status` LIKE 'paid'
GROUP BY `service`

Output:

service 10_total 15_total 20_total SUM(`amount`)
ABCD 3 50 63 1673.00
DEFG 22 1 98 2734.82
HIJK 2 0 22 437.10
...

What I want is this:

service 10_total 10_sum 15_total 15_sum 20_total 20_sum
ABCD 3 35.00 50 600.00 63 1038.00
DEFG 22 735.00 1 17.00 98 1982.82
HIJK 2 35.00 0 NULL 22 402.10
...

Thanks again!

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
The point I couldn't understand on this thread was,

if the expressions like this were already gotton
COUNT( CASE WHEN LENGTH(`pin`) = 10 THEN 1 END ) AS 10_total ,
SUM(`amount`)
why not reach to the following function/expression(combined the two expressions)?
SUM( CASE WHEN LENGTH(`pin`) = 10 THEN `amount` END ) AS 10_sum

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Argh! Tonkuma hit the problem exactly, and that's really what I meant to post... I ran something through my "pretty printer" and obviously caught the wrong code snippet. My bad!

What I'd meant to post was:
Code:
```SELECT `service`
,  Count(CASE WHEN 10 = Length(`pin`) THEN        1 END) AS 10_cnt
,  Sum(  CASE WHEN 10 = Length(`pin`) THEN `amount` END) AS 10_sum
,  Count(CASE WHEN 15 = Length(`pin`) THEN        1 END) AS 15_cnt
,  Sum(  CASE WHEN 15 = Length(`pin`) THEN `amount` END) AS 15_sum
,  Count(CASE WHEN 20 = Length(`pin`) THEN        1 END) AS 20_cnt
,  Sum(  CASE WHEN 20 = Length(`pin`) THEN `amount` END) AS 20_sum
,  Sum(`amount`) AS all_sum
FROM `table`
WHERE  `paidDateTime` > NOW() - INTERVAL 30 day
AND `status` LIKE 'paid'
GROUP BY `service`;```
-PatP

6. Registered User
Join Date
Aug 2012
Posts
9
That is exactly what I needed. Awesomely awesome awesomeness! Thank you both so much!

7. Registered User
Join Date
Aug 2012
Posts
9
Quick question.

I noticed that you wrote the case statements as

COUNT(CASE WHEN 10 = LENGTH(`pin`) THEN 1 END) AS 10_cnt,

whereas I used

COUNT(CASE WHEN LENGTH(`pin`) = 10 THEN 1 END) AS 10_cnt,

Both work. Is this just a matter of preference or am I writing it incorrectly?

Thanks again.

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I'm an olde phart who has had to deal with many "interesting" SQL "features" over the years. I've learned that putting the constant first avoids a whole class of problems with compilers/interpreters/etc. and it also makes it quicker for mere mortals like me to read the code.

Are you doing anything wrong, no. Does "constant first" work better for me so that's how I built my "pretty printer", yes!

-PatP

9. Registered User
Join Date
Aug 2012
Posts
9
Once again, thank you for the education and insight. I truly appreciate it.

10. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
COUNT(CASE WHEN 10 = LENGTH(`pin`) THEN 1 END) AS 10_cnt,

You can also use
COUNT(CASE 10 WHEN LENGTH(`pin`) THEN 1 END) AS 10_cnt,

MySQL :: MySQL 5.6 Reference Manual :: 12.4 Control Flow Functions :: CASE operator

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•