CODEWITHSUNDEEP
mysqlsql
Awemo
Awemo

Reputation: 895

Using DATE_FORMAT to group in mysql

I am writing an SQL query in Mysql where the result should be grouped by both an ID and the date. The date is determined dynamically, and can either be a simple datetime field, or a combination of the year and month or week of the year. See the query below for the week of the year case;

SELECT t.transactionId, t.transactionDate, t.transactionProcessDate, 
t.status, t.type, t.versionId, t.note, WEEKOFYEAR(t.transactionDate) as CW, 
YEAR(t.transactionDate) as yr, (DATE_FORMAT(t.transactionDate, '%Y-%u')) as tDate
FROM transaction t 
WHERE (t.status = 'A' or t.status = 'N') 
GROUP BY t.transactionId , tDate

The problem I have is attempting to group by the tDate,

GROUP BY t.transactionId , tDate

which returns duplicates with same dates.

When I change the query to group by the CW and yr fields, 

GROUP BY t.transactionId , CW, yr

I get the expected result, without duplicates.

Question: Is there a problem with grouping by fields generated by the DATE_FORMATwhich may result in the returned query not being able to recorgnize duplicates?
I would like to completely exclude the two fields CW and yr from the query since they are used only for grouping.

Upvotes: 1

Views: 7683

Answers (1)

peterm
peterm

Reputation: 92785

UPDATED First of all, it's hard to tell not seeing your real data, but GROUP BY DATE_FORMAT(t.transactionDate, '%Y-%u') works just fine

SELECT transactionId, 
       DATE_FORMAT(transactionDate, '%Y-%u') tDate
       ...
  FROM transaction
 WHERE status IN('A', 'N') 
 GROUP BY transactionId, tDate

Here is SQLFiddle

On a side note: even though MySql allows to specify a field (in your case t.status, t.type etc.) in SELECT that is not a part of GROUP BY it's a not a good thing to do. You need to apply an aggregate function to that field (MAX, MIN...). There is no way to tell deterministically which value of such a field to grab when you do GROUP BY

Upvotes: 4

Related Questions