Problem of SUM of the SUM

Question

When I execute my query I have always the same problem :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|Total
------------------------------------------------------------
  2016       |    7        |  0     | 0.40   |  0      0.40
  2016       |    7        |  0.50  | 0      |  0      0.50
  2016       |    7        |  0     | 0      |  0.30   0.30

How I can combine to have a single line :

 year_created|month_created|loc_id_1|loc_id_2|loc_id_3|
-------------------------------------------------------
  2016       |    7        |  0.50  | 0.40   |  0.30

This is my query :

SELECT
   MONTH(created_at) as month_created,
   YEAR(created_at) as year_created,
   1 - SUM(price)/SUM(order_total) as Total,
   CASE loc_id WHEN 1 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_1',
   CASE loc_id WHEN 2 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_2',
   CASE loc_id WHEN 3 THEN  1 - SUM(price)/SUM(order_total) ELSE 0 END ) AS 'loc_id_3'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
  GROUP BY year_created, month_created
  ORDER BY year_created asc, month_created desc

I have good values with my request but are not grouped. I want a method to replace the sum of sum to have a signle ligne per month. — Sarah, Jul 12 '16 at 11:51
Show us the query that produces this result (the 3 rows). Or show us the original data of the table. — ypercubeᵀᴹ, Jul 14 '16 at 07:18

Marco · Answer 1 · 2016-07-12T12:06:54.960

Problem is that the SUM(order) can be 0 in the CASE statement. Create a sub-query that delivers the price and order totals per loc_id and then use this to do the calculation if the sum of order is not 0:

SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order'

Then the SQL look something like (check if this is possible in vertica):

SELECT year_created, month_created, 1 - total
       1 - CASE loc_id_1_order WHEN 0 THEN 0 ELSE loc_id_1_price/loc_id_1_order END as loc_id_1,
       1 - CASE loc_id_2_order WHEN 0 THEN 0 ELSE loc_id_2_price/loc_id_2_order END as loc_id_2,
       1 - CASE loc_id_3_order WHEN 0 THEN 0 ELSE loc_id_3_price/loc_id_3_order END as loc_id_3
FROM
  (SELECT
     MONTH(created_at) as month_created,
     YEAR(created_at) as year_created,
     SUM(price)/SUM(order_total) as Total,
     SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
     SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order',
     SUM(CASE loc_id WHEN 2 THEN price ELSE 0 END) AS 'loc_id_2_price',
     SUM(CASE loc_id WHEN 2 THEN order ELSE 0 END) AS 'loc_id_2_order',
     SUM(CASE loc_id WHEN 3 THEN price ELSE 0 END) AS 'loc_id_3_price',
     SUM(CASE loc_id WHEN 3 THEN order ELSE 0 END) AS 'loc_id_3_order'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
   GROUP BY year_created, month_created)
ORDER BY year_created asc, month_created desc

Then you must get rid of the 'outer' SUM. I added it to my answer. — Marco, Jul 12 '16 at 10:15

score -1 · Answer 2 · answered Jul 14 '16 at 05:55

Try this:

select distinct year_created, month_created,
  max(loc_id_1) over (partition by year_created, month_created) as loc_id_1,
  max(loc_id_2) over (partition by year_created, month_created) as loc_id_2,
  max(loc_id_3) over (partition by year_created, month_created) as loc_id_3,
  sum(loc_id_1) over (partition by year_created, month_created) + sum(loc_id_2) over (partition by year_created, month_created) + sum(loc_id_3) over (partition by year_created, month_created) as total
from (
  -- your query
) z;

Or in general, read up on analytic queries.

Problem of SUM of the SUM

2 Answers2