hocuspocus31 hocuspocus31 - 5 months ago 15
SQL Question

Sum Union Query and get percentage for each row

I'm trying to get the percentage of this query per row. So my query is this:

SELECT ITEM, AVG_VAL/sum(AVG_VAL) * 100 as PERCENT_TOTAL

FROM
(SELECT
'item 1' AS ITEM,
AVG(VAL) AS AVG_VAL

FROM TABLE1
WHERE DATE(t_stamp) BETWEEN '2016-06-02' AND '2016-06-02'

UNION ALL

SELECT
'item 2' AS ITEM,
AVG(VAL) AS AVG_VAL

FROM TABLE2
WHERE DATE(t_stamp) BETWEEN '2016-06-02' AND '2016-06-02') as t


The data for the first table (TABLE1) is

ITEM | AVG_VAL
item 1 | 45,042,179


The data for the second table (TABLE2) is

ITEM | AVG_VAL
item 2 | 30,428,453


I was hoping to get the output

ITEM | PERCENT_TOTAL
item 1 | 59.68
item 2 | 40.32


but when i try to group it by ITEM

SELECT ITEM, AVG_VAL/sum(AVG_VAL) * 100 as PERCENT_TOTAL

FROM
(SELECT
'item 1' AS ITEM,
AVG(VAL) AS AVG_VAL

FROM TABLE1
WHERE DATE(t_stamp) BETWEEN '2016-06-02' AND '2016-06-02'

UNION ALL

SELECT
'item 2' AS ITEM,
AVG(VAL) AS AVG_VAL

FROM TABLE2
WHERE DATE(t_stamp) BETWEEN '2016-06-02' AND '2016-06-02') as t
GROUP BY ITEM


I get

ITEM | PERCENT_TOTAL
item 1 | 100
item 2 | 100


I was also hoping I can show the total for the percent_total column, so that it will show

ITEM | PERCENT_TOTAL
item 1 | 59.68
item 2 | 40.32
Total | 100

Answer

Here's one way...

SELECT x.*
     , ROUND(avg_val/total * 100,2) pct
  FROM
     ( SELECT * FROM table1
        UNION
       SELECT * FROM table2
     ) x
  JOIN 
     ( SELECT SUM(avg_val) total
         FROM 
            ( SELECT * FROM table1
               UNION
              SELECT * FROM table2
            ) n
     ) y
Comments