SELECT data grouped by WEEK in SQL

Question

I have a working query (ORACLE SQL) that gives me gallons grouped by store number, with gallons summed by type and a percentage column as well. Each store number has a different conversion date from which I sum up the data -

SELECT StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ClrntSys IN ('844', '84448', 'GIC')
AND ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY StoreNbr
ORDER BY StoreNbr;

I now need to also sum it up by week, with Sunday being the first day of each week, but I'm having trouble understanding how DATEPART works. Just so I could get a better idea, I tried only to sum up the data for last week using DATEPART examples I'm seeing online, but this doesn't work. It's giving me "invalid identifier DATEPART". -

SELECT DATEPART(week, 5/17/20) AS weekTotal, 
StoreNbr, 
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END)) AS DECIMAL (5,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY DATEPART(week, 5/17/20), StoreNbr
ORDER BY StoreNbr;

What I really need, however, is each week's data (with Sunday being the beginning of each week) summed up separately going back to each store's conversion date. Is it possible to do that? Is there something else besides DATEPART that would work better?

It is important for us to know your DBMS in order to answer this. `DATEPART(week, 5/17/20)` looks awfully wrong. 5/17/20 = 0.0147058823. I doubt that you want this to get calculated. It *may* be you want `DATEPART(week, DATE '2020-05-14')`, but I cannot be sure and I don't know what your DBMS considers the first day of the week either. And please add sample data and expected result, so we are sure to understand `each week's data summed up separately going back to each store's conversion date`. — Thorsten Kettner, May 26 '20 at 12:54

Brian Hoffman · Accepted Answer · 2020-05-26T13:51:42.780

1

Sorry - just noticed that you said Oracle SQL, and my first answer was for SQL Server! The reason you are getting an error is that DATEPART is not an Oracle function. Instead, you can simply do math on the dates, using a known sunday (prior to a known first date in the DB table) as an anchor date:

SELECT
    '30-DEC-2018' as "Known Sunday,
    trunc((sysdate - to_date('30-DEC-2018')) / 7) as "Week Num",
    to_date('30-DEC-2018') 
        + (trunc((sysdate - to_date('30-DEC-2018')) / 7) * 7)"
FROM
    dual

edited May 26 '20 at 13:51

answered May 26 '20 at 13:35

Brian Hoffman

24
3

Thank you - was able to rework TRUNC to work in the query by using a specific date column in the same table, and GROUP BY and ORDER BY to get the data just the way I needed it! – BigRedEO May 26 '20 at 19:49

SELECT data grouped by WEEK in SQL

1 Answers1