1

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

closed as unclear what you're asking by mustaccio, Marco, John aka hot2use, Vérace, Colin 't Hart Mar 13 at 12:13

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • How about the value for total? – Marco Jul 12 '16 at 10:55
  • i added the value of total – Sarah Jul 12 '16 at 11:45
  • 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 7:18
0

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
  • it not give me the good results. – Sarah Jul 12 '16 at 9:54
  • Then you must get rid of the 'outer' SUM. I added it to my answer. – Marco Jul 12 '16 at 10:15
  • it dosen't work. – Sarah Jul 12 '16 at 10:25
  • with this method i will have 3 select – Sarah Jul 12 '16 at 12:24
-1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.