Photographer Britt Photographer Britt - 3 months ago 10
MySQL Question

MYSQL Subquery on the same table with Group By Clauses...

having what's probably another simple left/right join with mysql... but it's a bit complex and all the examples I've seen so far I can't seem to make work with what I have. I apologize if this is a bit confusing... I'm going to try to really spell it out. Basically, there's three SELECT queries that are selecting data from the SAME table. There's only four fields I'm interacting with in a table called 'Order_Items' which is:


  • 'Item' - The Item name, however this CAN be blank, when it is I need to get:

  • 'Essential_Type' - The alternate Item Name

  • 'QTY' - Which is how many have been ordered of that item that was ordered in the particular order. Now, if Item is blank, I need to COUNT(Essential_Type) instead of SUM(QTY) because QTY for those rows is Null, but that row needs to be counted as 1

  • 'Created' - The DateTime of the order creation






Query #1: This gets me the Name of the item and how many were Sold

SELECT
IF(Item='', Essential_Type, Item) AS Name,
IF(Item='', COUNT(Essential_Type), SUM(QTY)) AS Qty
FROM Order_Items
GROUP BY Item, Essential_Type
ORDER BY Qty


Returns:

NAME QTY
---------------------
8x10 2345
16x20 340





Query #2: This gets me the number of each item sold THIS year

SELECT
IF(Item='', Essential_Type, Item) AS Name,
IF(Item='', COUNT(Essential_Type), SUM(QTY)) AS ThisYear
FROM Order_Items
WHERE year(Created)=2016
GROUP BY Item, Essential_Type


Returns:

Name ThisYear
-----------------------------
8x10 120
16x20 25





Query #3: This gets me the number of each item sold LAST year

SELECT
IF(Item='', Essential_Type, Item) AS Name,
IF(Item='', COUNT(Essential_Type), SUM(QTY)) AS LastYear
FROM Order_Items
WHERE year(Created)=2015
GROUP BY Item, Essential_Type


Returns:

Name LastYear
-----------------------------
8x10 500
16x20 30








So if Order_Items looks like this for example:

Item Essential_Type QTY Created
---------------------------------------------------------------------
8x10 3 2016-09-01
8x10 1 2015-07-21
16x20 NULL 2015-08-12
16x20 NULL 2016-05-21


How can I combine those three somewhat complex SELECTs into one SELECT statement using a JOIN? Basically so the final result would be (based on the above sample table):

Name QTY ThisYear LastYear
-----------------------------------------------------------
8x10 4 3 1
16x20 2 1 1


Thanks for reading and any potential help!

Answer

I find the logic for the aggregation awkward (I suspect that the query would be simpler using UNION ALL). But, you can extend your query using conditional aggregation:

SELECT (CASE WHEN Item = '' THEN Essential_Type ELSE Item END) AS Name,
       (CASE WHEN Item = '' THEN COUNT(Essential_Type) ELSE SUM(QTY) END) AS Qty,
       (CASE WHEN Item = ''
             THEN COUNT(CASE WHEN YEAR(Created) = 2015 THEN Essential_Type END)
             ELSE SUM(CASE WHEN YEAR(Created) = 2015 THEN QTY END)
        END) as yr2015,
       (CASE WHEN Item = ''
             THEN COUNT(CASE WHEN YEAR(Created) = 2016 THEN Essential_Type END)
             ELSE SUM(CASE WHEN YEAR(Created) = 2016 THEN QTY END)
        END) as yr2016
FROM Order_Items
GROUP BY Item, Essential_Type
ORDER BY Qty;