CODEWITHSUNDEEP
mysqlsql
Eric T
Eric T

Reputation: 1026

SQL distinct on Date and ignore Time

I would love to distinct the table below based on Dates only and at the same time there is an average of value taken. Is there a way to do so?

SELECT DISTINCT DATE_FORMAT(Date, '%Y-%m-%d'),
      AVG(value) AS Timestamp 
FROM abc GROUP BY Date

Table abc

+--------------------+-------+
| Date               | value |
+--------------------+-------+
| 18-Nov-2013 12:34  |   5   |
| 19-Nov-2013 14:45  |   4   |
| 20-Nov-2013 15:11  |   3   |
| 21-Nov-2013 08:33  |   2   |
| 21-Nov-2013 11:11  |   7   |
| 21-Nov-2013 15:15  |   8   |
+--------------------+-------+

Upvotes: 1

Views: 1903

Answers (2)

Hett
Hett

Reputation: 3824

You need to use GROUP function

SELECT DATE_FORMAT(Date, '%Y-%m-%d') AS Timestamp, AVG(value)
FROM abc
GROUP BY DATE(Date)

Upvotes: 1

Deep
Deep

Reputation: 3202

you can do this in this way :

SELECT DATE_FORMAT(Dat, '%Y-%m-%d') AS dt,
       Avg(val)                     AS Timestamp
FROM   abc
GROUP  BY dt

you can group by by evaluated column using its alias in MySQL like I did in above query. Some DBMS like SQL Server doesn't allow such SQL so in that case you can use either subquery or group by function like :

SELECT dt,
       Avg(val)
FROM   (SELECT DATE_FORMAT(Dat, '%Y-%m-%d') AS dt,
               val
        FROM   abc) t
GROUP  BY dt 

SELECT DATE_FORMAT(Dat, '%Y-%m-%d') AS datetime,
       Avg(val)                     AS Timestamp
FROM   abc
GROUP  BY DATE_FORMAT(Dat, '%Y-%m-%d')

sqlfiddle

Upvotes: 2

Related Questions