Grouping by quarter

Question

How do you group weekly data into quarterly data in SQL?

I'd like the code below to return the columns selected in the statement but sum up sales by quarter rather than return weekly data.

My code:

select
    pz.PriceZoneID,
    pz.Name,
    es.ClientDepartmentID,
    es.DepartmentName,
    es.ClientCategoryID,
    es.CategoryName,
    es.ClientSubCategoryID,
    es.SubCategoryName,
    es.ClientProductID,
    es.ProductName,
    ash.Sales as Units, 
    ash.price * ash.Sales as CashSales, 
    ash.Cost, 
    CAST(ash.date as Date) as Date 
from aggregatedsaleshistory as ash 
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSID
join PriceZone as pz on pz.PriceZoneID = ash.PriceZoneID 
WHERE es.SubCategoryName = 'Yogurt'
GROUP BY 
    pz.PriceZoneID,
    pz.Name,
    es.ClientDepartmentID,
    es.DepartmentName,
    es.ClientCategoryID,
    es.CategoryName,
    es.ClientSubCategoryID,
    es.SubCategoryName,
    es.ClientProductID,
    es.ProductName,
    ash.Cost,
    ash.Date

The output has the weekly dates in the last column. So my question is how can I group the weekly data into quarters? I'd paste a sample below here, but I'm not sure how to provide sample data here? A sample I'd just pasted came out in a strange format when I saved the edit!

Thanks in advance?

You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh, Aug 15 '18 at 15:13
I'm not sure how to answer your first question without table structure, sample data, and expected results. The error message doesn't make much sense. Also I would like to mention that you shouldn't be grouping by fields that you include in aggregates -- remove `sales` and `price` from the group by... — sgeddes, Aug 15 '18 at 15:16
Remove `ash.Sales` and `ash.price * ash.sales` from the GROUP BY list. They're the aggregated columns. — Ken White, Aug 15 '18 at 15:16
Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. — Tab Alleman, Aug 15 '18 at 15:22
Hi sgeddes and Ken White - thank you for the suggestion of removing ash.sales and ash.price * ash.sales calcs from the GROUP BY list. Good spot!! I still got the error message but it went away when I removed the SUM function from the ash.sales and ash.price * ash.sales lines in the SELECT list. I still have the query about grouping the data by quarter, though, so any thoughts would be appreciated. Thanks in advance. I've modified the original question. — Mr 2017, Aug 16 '18 at 11:22
Thanks jarlh for your answer about the GROUP BY list, it was useful. — Mr 2017, Aug 16 '18 at 11:22

score 0 · Answer 1 · answered Aug 15 '18 at 15:15

0

The particular error message does not make sense because you explicitly do have pz.PriceZoneID in the GROUP BY. This could happen if you have a semicolon before the GROUP BY (I don't see one in the query in the question).

But you still have a problem. You are aggregating by too many columns. The group by should be:

GROUP BY
    pz.PriceZoneID,
    pz.Name,
    es.ClientDepartmentID,
    es.DepartmentName,
    es.ClientCategoryID,
    es.CategoryName,
    es.ClientSubCategoryID,
    es.SubCategoryName,
    ash.SegmentID,
    es.ClientProductID,
    es.ProductName

These are all the non-aggregated columns in the SELECT, except ash.cost, which probably should not be there anyway.

answered Aug 15 '18 at 15:15

Gordon Linoff

1,242,037
58
646
786

Thanks for your answer, Gordon. Do you know how many columns is "too many?" Or is there a limit to the number of columns one should use when GROUPING BY? The invalid error message went away when I removed the SUM function from the ash.sales and ash.price * ash.sales lines. However, I've still got the question about GROUPING the data by quarter. Thanks in advance. – Mr 2017 Aug 16 '18 at 11:25
@Mr2017 . . . "too many" means that you are aggregating columns that are arguments to aggregation functions, such as `ash.price * ash.Sales` (which I see has been edited out). – Gordon Linoff Aug 16 '18 at 12:16

Grouping by quarter

1 Answers1