-1

I am using standard SQL and am trying to add the weekly sum for product usage by week.

Using code below, I was able to add to each row the respective week and year it falls into. How would I go about summing the totals for an item by week and outputting it in columns, say up to the last 8 weeks.

extract(week from Metrics_Date) as week, EXTRACT(YEAR FROM Metrics_Date) AS year

Image is my raw data with the week and year next to an item:

enter image description here

This image is of above raw data being analyzed further(grouping them together). Here is where I would want to add columns, current_week & firstday of week date, and a sum of that weeks totals. enter image description here

Any help would be appreciated.

zstar
  • 47
  • 1
  • 7
  • Does this answer your question? [How to SUM two fields within an SQL query](https://stackoverflow.com/questions/14877797/how-to-sum-two-fields-within-an-sql-query) – oittaa Apr 14 '20 at 00:22
  • In image 2, I need a new column say, last week, next to Last1TotalRequests, where it SUMS the message_count from image 1, grouped by company_name, product_name for the last actual week – zstar Apr 14 '20 at 00:55
  • In order to further help you I need to check if I understood correctly, you want first to sum a column grouped per week , so each week number will have a certain SUM. Then , you want to display only last week's sum based in the current week. Is this what you want ? – Alexandre Moraes Apr 14 '20 at 06:34

1 Answers1

4

You don't need the extract() by the way, you can do truncation DATE_TRUNC(your_date, WEEK) and it will truncate it to the week, usually easier.

Also, because the result of the truncation is a date, you will have the first day of the week already.

The rest I believe you have it figured out already, but just in case:

SELECT DATE_TRUNC(your_date_field, WEEK) AS week, SUM(message_count) AS total_messages FROM your_table GROUP BY 1

Francesco Galletta
  • 698
  • 3
  • 8
  • thanks for telling me about DATE_Trunc. I guess I had it solved but leaving this answer for future users. – zstar Apr 14 '20 at 21:10