SQL: While and Union to unify SELECTs

Question

Let's try this way.

As you can see, I have many SELECT's (I'm showing just 3 but I have more than 20) and I need to unify all of them. Everytime when I need to add a Brand, I have to change ALL the SELECT's, one by one. This is quite boring.

Also, I have more than 10 Brands (not only 3) and I need to type one by one too. This is not so bad if I can change just 1 time and let the WHILE LOOP do the rest for the other SELECTs. But if I have to change all the brands for all SELECT's, it takes me a lot of time.

DECLARE @COUNTRY varchar(30)
SET @COUNTRY = 'GERMANY'

SELECT * FROM
(SELECT * FROM
    (SELECT
        FOOD_CATEGORY,
        YEAR_SALE,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND A' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3) 
        END AS Share_A,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND B' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_B,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND C' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_C
    FROM tableChicken
    WHERE
        AREA = @COUNTRY
    GROUP BY
        FOOD_CATEGORY,
        YEAR_SALE) AS CHICKEN

UNION

SELECT * FROM

    (SELECT
        FOOD_CATEGORY,
        YEAR_SALE,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND A' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3) 
        END AS Share_A,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND B' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_B,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND C' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_C
    FROM tableSwine
    WHERE
        AREA = @COUNTRY
    GROUP BY
        FOOD_CATEGORY,
        YEAR_SALE) AS SWINE

UNION

SELECT * FROM

    (SELECT
        FOOD_CATEGORY,
        YEAR_SALE,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND A' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3) 
        END AS Share_A,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND B' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_B,
        CASE WHEN SUM(SALES)=0 THEN 0
        ELSE ROUND(SUM(CASE WHEN FABRICANTE='BRAND C' THEN ROUND(SALES,3) ELSE 0 END)/SUM(SALES),3)
        END AS Share_C
    FROM tableTurkey
    WHERE
        AREA = @COUNTRY
    GROUP BY
        FOOD_CATEGORY,
        YEAR_SALE) AS TURKEY
) AS MAIN_BASE

ORDER BY FOOD_CATEGORY, YEAR_SALE

I hope it's understandable now. Thanks for helping!

Is your database mysql or sql-server? – Juan Carlos Oropeza Oct 15 '15 at 22:27 — Juan Carlos Oropeza, Oct 15 '15 at 22:27

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

I know this is a simply version of your code, but cant you do something like this.

SELECT Brand
     , SUM(Sales_Kg)  AS Sales_Volume
FROM tblSales
WHERE Year = 2015 
  and ID_Brand IN (1,2,3)  -- ADD THIS LINE
GROUP BY Brand

I really dont understand why your complicate your query with a CASE when the group by already set the brand_id

Otherwise use a temporary table to save each result SAMPLE

edited May 23 '17 at 12:23

Community

1
1

answered Oct 15 '15 at 22:10

Juan Carlos Oropeza

47,252
12
78
118

Thanks Juan. I know that this way would be way too easier, but actually my code is much more complicated. – Kreiven Oct 16 '15 at 17:52
I'm using several CASE's, one for each brand, and then one select for each food category (I work in a huge food company and we have several brands and categories). So, I don't want to type every brand name for every food category (chicken, sausage, franks, ham, light cold cuts, etc). I'd like to use a WHILE to select all the categories and brands without having to type one by one. So, I'm trying to understand how can I use the WHILE function here in order to solve my problem. – Kreiven Oct 16 '15 at 17:58
That is the problem if you make the question too simple. My query and Gordon do exactly what you ask for. Maybe you need make another question with details of other parts you have problem with. Try read [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Oct 16 '15 at 18:40
Again for you example the use of `CASE` inside the sum doesnt make sense because doesnt add anything. All the sum are already same `brand_id` inside a `group by` You next example should include brand and category but only a few of each. No need include the whole set. If work for 2-3 proably will work for 5+ – Juan Carlos Oropeza Oct 16 '15 at 18:52

score 0 · Answer 2 · answered Oct 15 '15 at 22:19

0

I find the logic strange, but you can use a cross join:

SELECT Brand,
       (CASE WHEN SUM(Sales_Kg)=0 THEN 0
             ELSE SUM(CASE WHEN ID_Brand = b.id_brand THEN Sales_Kg ELSE 0 END)  
        END) AS Sales_Volume
FROM tblSales s CROSS JOIN
     (SELECT 1 as id_brand UNION ALL SELECT 2 UNION ALL select 3) b
WHERE Year = 2015
GROUP BY Brand, b.id_brand;

You probably want to include b.id_brand in the select as well.

answered Oct 15 '15 at 22:19

Gordon Linoff

1,242,037
58
646
786

@Kreiven If you have a more complex query. This method do exactly the same as your store proc. But to me you just calculate total of `sales_kg` for each brand. And that is what `SUM(sales_kg) group by Brand` does. – Juan Carlos Oropeza Oct 15 '15 at 22:32

SQL: While and Union to unify SELECTs

2 Answers2