Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    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. #4
    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. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

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

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

  10. #10
    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
  •