SQL query to break down day by day for entire week-CodePudding

I have a table of sales in MySQL. I'm trying in 1 query to get a view that looks like this:

AGENT, MONDAY_TOTAL, TUESDAY_TOTAL,WEDNESDAY_TOTAL,THURSDAY_TOTAL,FRIDAY_TOTAL,SATURDAY_TOTAL

What I have so far is this:

SELECT DISTINCT(repname), DAYOFWEEK(sub_date), COUNT(*) 
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) 
GROUP BY repname, DAYOFWEEK(sub_date)

That gives me values that look like this:

AGENT, DAYOFWEEK, TOTAL

Naturally, I can turn the output of the second example into the first in my code, but if I can just do it with the SQL query I'd rather do that.

SELECT repname, 
       SUM(IF(DAYOFWEEK(sub_date)=2, 1, 0)) AS MONDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=3, 1, 0)) AS TUESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=4, 1, 0)) AS WEDNESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=5, 1, 0)) AS THURSDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=6, 1, 0)) AS FRIDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=7, 1, 0)) AS SATURDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=1, 1, 0)) AS SUNDAY_TOTAL
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) 
GROUP BY repname

But getting results for days that haven't happened yet.

Update: This was because we did not constrain the year:

Solution:

SELECT repname, 
       SUM(IF(DAYOFWEEK(sub_date)=2, 1, 0)) AS MONDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=3, 1, 0)) AS TUESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=4, 1, 0)) AS WEDNESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=5, 1, 0)) AS THURSDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=6, 1, 0)) AS FRIDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=7, 1, 0)) AS SATURDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=1, 1, 0)) AS SUNDAY_TOTAL
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) AND YEAR(sub_date) = YEAR(CURRENT_DATE)
GROUP BY repname

CodePudding user response：

You can use an IF function to select only DAYOFWEEK-related counts, then sum every counted element inside the SUM aggregate function as follows:

SELECT repname, 
       SUM(IF(DAYOFWEEK(sub_date)=2, 1, 0)) AS MONDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=3, 1, 0)) AS TUESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=4, 1, 0)) AS WEDNESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=5, 1, 0)) AS THURSDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=6, 1, 0)) AS FRIDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=7, 1, 0)) AS SATURDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=1, 1, 0)) AS SUNDAY_TOTAL
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) 
GROUP BY repname

Note: when the DISTINCT keyword is applied, it will work on every selected field of your SELECT clause, you can't make a distinct of a single field using parentheses.