Results 1 to 10 of 10

010614, 23:02 #1Registered 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.

010614, 23:54 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Code: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`
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

010714, 00:31 #3Registered 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!

010714, 07:28 #4Registered 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`)
SUM( CASE WHEN LENGTH(`pin`) = 10 THEN `amount` END ) AS 10_sum

010714, 08:23 #5Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Argh! 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`;
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

010714, 09:25 #7Registered 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.

010714, 09:34 #8Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54I'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!
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

010714, 10:19 #10Registered 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