CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5105

day of week function for week day aggregates

I currently have a query that reads from a table and aggregates based on category. It gives me what I need but I"m trying to add another column that looks at all records for that category/employee combo for the days of this past week. SO if the job with this query runs on Wednesday Night, it needs to get a total of all category/employee records for Monday and Tuesday Night as well.

The query:

SELECT employee,
    sum(case when category = 'Shoes' and  date_of_report >=  current_date - 1 days then daily_total else 0 end) as Shoes_DAILY,
    sum(case when category = 'Shoes' and date_of_report >= ( current date - ( dayofweek(current date) - 1 ) days ) then sum(daily_total) else 0 end) as dailyTotalWeek
from shoeTotals
where date_of_report >= current_date
group by employee;

So the third column there is what's messing me up saying function use not valid. here's what I want:

The source table has these records for this past week:

employee   |   daily_total   |   date_of_report
--------------------------------------------------
123             14                  2019-08-26
123             1                   2019-08-27
123             56                  2019-08-28
123             6                   2019-08-29
123             8                   2019-08-30   * today

My desired output would get (based on employee and category) the total for today (8) and then the sum of all the employees' records for that category on each preceding weekday. Running on Monday night would only count that days records, friday night would count monday through friday's as shown above.

employee   |   shoes_daily   |    dailyTotalWeek 
--------------------------------------------------
123                 8                85

What am I doing wrong with the dayofweek function?

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You cannot nest aggregation functions. I think you simply want:

select employee,
       sum(case when category = 'Shoes' and date_of_report >=  current_date - 1 days
                then daily_total else 0
           end) as Shoes_DAILY,
       sum(case when category = 'Shoes' and date_of_report >= ( current date - ( dayofweek(current date) - 1 ) days )
                then daily_total else 0
           end) as dailyTotalWeek
from shoeTotals
where date_of_report >= current date - ( dayofweek(current date) - 1 ) days
group by employee;

Upvotes: 0

Related Questions