SQL Group By with Case

Question

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.

ScaisEdge · Accepted Answer · 2016-10-21T18:26:05.073

2

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 ,
    aoi.is_animal,
    CASE
        WHEN aot.name = 'Misc.' THEN 'Y'
        ELSE 'N'
    END

edited Oct 21 '16 at 18:26

answered Oct 21 '16 at 18:06

ScaisEdge

131,976
10
91
107

Seems to work as long as I remove the aliases on the `GROUP BY` clause. – dokgu Oct 21 '16 at 18:10
In oracle the query should work also using the columns alias .. i have used the flat code just for sample ..because aot.name is not equivalent to the case for display_name ..anyway i have update the asnwer also for alias ... let me know – ScaisEdge Oct 21 '16 at 18:15
1

Nope it doesn't work. I get an error saying `ORA-00904: "show_details": invalid identifier`. – dokgu Oct 21 '16 at 18:17
@scaisEdge - what the OP is saying is that the "AS show_details" at the end does not work - and obviously it doesn't (I assume in ANY flavor of SQL). Of course the other aliases do work, throughout the CASE expression; but you can't alias the result of the CASE expression in GROUP BY. – Oct 21 '16 at 18:19
@mathguy . answer updated remobìving the alias group by – ScaisEdge Oct 21 '16 at 18:22
@scaisEdge You need to remove the `AS display_name` and `AS show_details` on the group by clause. – dokgu Oct 21 '16 at 18:25
@PatrickGregorio . yes correct .. copy and paste with half brain .. off .. .. i have udpdate the answer – ScaisEdge Oct 21 '16 at 18:26
@scaisEdge as long as you can still turn it on you're okay. Thanks for the solution! – dokgu Oct 21 '16 at 18:27
@PatrickGregorio many .. thanks my brain model ..is an old model ... not full multitasking .. – ScaisEdge Oct 21 '16 at 18:29

score 1 · Answer 2 · answered Oct 21 '16 at 18:06

1

I think this will do what you want:

SELECT (CASE WHEN aoi.is_animal = 'N' THEN TO_CHAR(aot.name)
             ELSE 'Boarding Charges'
        END) AS display_name,
       (CASE WHEN aoi.is_animal = 'N' THEN aoi.is_animal END) as is_animal,
       (CASE WHEN aoi.is_animal <> 'N' THEN NULL
             WHEN aot.name = 'Misc.' THEN 'Y'
             ELSE 'N'
        END) AS show_details,
       SUM(aoi.quantity * aoi.unit_price) as total
FROM . . .

The idea is to set the other two keys to NULL for that "Boarding Charges" rows. The GROUP BY should change to match the SELECT columns.

answered Oct 21 '16 at 18:06

Gordon Linoff

1,242,037
58
646
786

1

This did fix the problem of not being able to group all the **Boarding Charges** but I don't understand what the purpose of having a `null` on the other 2 columns. I think I prefer the answer of @scaisEdge. - Still upvoted. – dokgu Oct 21 '16 at 18:23
@PatrickGregorio . . . Well, it would seem possible for the other columns to have different values for "Boarding Charges" rows. That would then spread the results onto multiple rows. This version *guarantees* that "Boarding Charges" are only on one row, regardless of the values of the other columns. Hence, it is a more resilient answer to the question. – Gordon Linoff Oct 22 '16 at 15:29

SQL Group By with Case

Additional Information

2 Answers2