CODEWITHSUNDEEP
mysqldatetimegroup-bycase
muazfaiz
muazfaiz

Reputation: 5031

MySQL: Calculate a new column (converting days to week) to get weekly sales of products

My table sales in mysql looks like this:

ID             Date     Quantity
00000001    01/01/2017  5
00000002    02/01/2017  25
00000003    01/01/2017  2
00000001    04/01/2017  58
00000005    01/01/2017  8
00000001    10/01/2017  2

I want to get weekly sales (week1 in this case) of each product. Following is my query

SELECT ID, 
    IF (YEARWEEK(Date) = '201401', SUM(Quantity), NULL) AS WEEK1
FROM sales
GROUP BY ID

it returns the following error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sales.registerDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have also used CASE statement but its not working fine for me. 

SELECT ID, 
    CASE WHEN  (YEARWEEK(Date) = '201401' THEN SUM(Quantity) END AS WEEK1
FROM sales
GROUP BY ID

It returns error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN SUM(Quantity) END) WEEK1
FROM sales
GROUP BY ID' at line 2

The expected result format is 

   ID     week1
00000001    63
00000002    25
00000003    2
00000005    8

What will be correct approach to get the desired results ?

Upvotes: 0

Views: 34

Answers (2)

gbtimmon
gbtimmon

Reputation: 4332

I would take out the case statement as it doesnt seem to be needed, you are just using it to select where YEARWEEK is '201401'. A simpler query might be:

SELECT
    ID
    , SUM(QUANTITY)
    , YEARWEEK(DATE) AS YEAR_WEEK
FROM 
    SALES 
WHERE 
    YEAR_WEEK = '201401'
GROUP BY
    ID
    , YEAR_WEEK

Upvotes: 1

Ian Kenney
Ian Kenney

Reputation: 6436

try summing the case statement, something like:

SELECT ID, 
    SUM(CASE WHEN YEARWEEK(Date) = '201401' THEN Quantity END) AS WEEK1
FROM sales
GROUP BY ID

Upvotes: 1

Related Questions