Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: Postgresql Case and Total

    Hi!

    I need help with this query

    Select CASE WHEN (lcode = '05' or lcode = '06' or lcode = '08' or lcode = '09' or lcode = '10') THEN 'All Student'
    WHEN (L1.code = '05' or Lcode = '06') THEN 'Maths Student'
    ELSE 'English Student'
    END as Decision,
    count(*) as Total
    from referral


    No record for Math Student are shown. I understand that the condition has been met by the first condition.


    If I change this to


    Select CASE WHEN (L1.code = '05' or Lcode = '06') THEN 'Maths Student'
    WHEN (lcode = '05' or lcode = '06' or lcode = '08' or lcode = '09' or lcode = '10') THEN 'All Student'

    ELSE 'English Student'
    END as Decision,
    count(*) as Total
    from referral

    The the sum for the 'All Student does not have the 'English student' numbers in it.


    How I make Postgresql also show the actual record of 'All Student and 'English student?


    Thank you.

    David

  2. #2
    Join Date
    Jul 2015
    Posts
    7
    try with window functions:

    Select CASE WHEN (L1.code = '05' or Lcode = '06') THEN 'Maths Student'
    WHEN (lcode = '08' or lcode = '09' or lcode = '10') THEN 'Non Maths Student'

    ELSE 'English Student'
    END as Decision,
    count(*) as Total,

    sum(count(*)) over () --'Non Maths Student' + 'Maths Student' + 'English Student'

    from referral

Posting Permissions

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