Patrick Gregorio Patrick Gregorio - 1 month ago 7
SQL Question

SQL Group By with Case

I need to create a simplified report where I list all of the sources of my sales and break it down only for miscellaneous stuff.

The reason why I said simplified is because I am trying to group some stuff together which means that all animal sales are supposed to be labeled Boarding Charges.

Here's my query so far:

SELECT
CASE
WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
ELSE 'Boarding Charges'
END AS display_name,
aoi.is_animal,
CASE
WHEN aot.name = 'Misc.' THEN 'Y'
ELSE 'N'
END AS show_details,
SUM(aoi.quantity * aoi.unit_price) as total
FROM ANIMAL_ORDER ao
LEFT JOIN ANIMAL_ORDER_ITEM aoi ON aoi.order_id = ao.id
LEFT JOIN ANIMAL_ORDER_TYPE aot ON aot.id = aoi.order_type_id
WHERE ao.order_stage != 'CANCELLED'
GROUP BY
aot.name,
CASE
WHEN aoi.is_animal = 'N' THEN 0
ELSE 1
END, aoi.is_animal


I'm trying to make this simple for now so I'm not worried about the
Misc.
stuff - I just added a column for it to say
Y
or
N
temporarily.

The query above results to something like this:

# Resulting table

DISPLAY_NAME IS_ANIMAL SHOW_DETAILS TOTALS
-------------------------------------------------------------
Boarding Charges Y N 8039.53
Truck Delivery Fee N N 1005.21
Misc. N Y 237.16
Cancellation Fee N N 45.00
Late Fee N N 410.25
Courier Fee N N 1338.40
Boarding Charges Y N 311.27
Boarding Charges Y N 7341.19


As you can see the Boarding Charges aren't grouped together and I understand what the reason is - I have
aot.name
in the
GROUP BY
clause. The only reason why it's there is because when I try to remove it, I get an error on
TO_CHAR(aot.name)
saying that it's not a
GROUP BY
expression.

I just want to have all the Boarding Charges grouped together and sum up their totals.

Additional Information



I'm trying to use the approaches mentioned in this question.

Answer

If you want a group by display_name you should repeat the same condition in group by

  SELECT
    CASE
        WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
        ELSE 'Boarding Charges'
    END AS display_name,
    aoi.is_animal,
    CASE
        WHEN aot.name = 'Misc.' THEN 'Y'
        ELSE 'N'
    END AS show_details,
    SUM(aoi.quantity * aoi.unit_price) as total
FROM ANIMAL_ORDER ao
LEFT JOIN ANIMAL_ORDER_ITEM aoi ON aoi.order_id = ao.id
LEFT JOIN ANIMAL_ORDER_TYPE aot ON aot.id = aoi.order_type_id
WHERE ao.order_stage != 'CANCELLED'
GROUP BY
    CASE
        WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
        ELSE 'Boarding Charges'
    END AS display_name,
    aoi.is_animal,
    CASE
        WHEN aot.name = 'Misc.' THEN 'Y'
        ELSE 'N'
    END AS show_details
Comments