Group by records by date

Refresh

December 2018

Views

58 time

1

I am using SQL Server 2008 R2. I am having a database table like below :

+--+-----+---+---------+--------+----------+-----------------------+
|Id|Total|New|Completed|Assigned|Unassigned|CreatedDtUTC           |
+--+-----+---+---------+--------+----------+-----------------------+
|1 |29   |1  |5        |6       |5         |2014-01-07 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|2 |29   |1  |5        |6       |5         |2014-01-07 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|3 |29   |1  |5        |6       |5         |2014-01-07 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|4 |30   |1  |3        |2       |3         |2014-01-08 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|5 |30   |0  |3        |4       |3         |2014-01-09 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|6 |30   |0  |0        |0       |0         |2014-01-10 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+
|7 |30   |0  |0        |0       |0         |2014-01-11 06:00:00.000|
+--+-----+---+---------+--------+----------+-----------------------+

Now, I am facing a strange problem while grouping the records by CreatedDtUTC column.

I want the distinct records from this table. Here you can observe that the first three records are duplicates created at the same date time. I want the distinct records so I had ran the query given below :

SELECT  Id, Total, New, Completed, Assigned, Unassigned, MAX(CreatedDtUTC) 
FROM TblUsage 
GROUP BY CreatedDtUTC

But it gives me error :

Column 'TblUsage.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I also have tried DISTINCT for CreatedDtUTC column, but had given the same error. Can anyone let me know how to get rid of this?

P.S. I want the CreatedDtUTC coumn in CONVERT(VARCHAR(10), CreatedDtUTC,101) format.

Dev

3 answers

2

Try this............

SELECT  min(Id) Id, Total, New, Completed, Assigned, Unassigned, CreatedDtUTC
FROM TblUsage 
GROUP BY Total, New, Completed, Assigned, Unassigned, CreatedDtUTC
0

Попробуй это:

SELECT MIN(Id) AS Id, Total, New, Completed, Assigned, Unassigned, 
       CONVERT(VARCHAR(10), CreatedDtUTC, 101) AS CreatedDtUTC
FROM TblUsage 
GROUP BY Total, New, Completed, Assigned, Unassigned, CreatedDtUTC

Проверьте DEMO SQL скрипку

ВЫХОД

| ID | TOTAL | NEW | COMPLETED | ASSIGNED | UNASSIGNED | CREATEDDTUTC |
|----|-------|-----|-----------|----------|------------|--------------|
|  1 |    29 |   1 |         5 |        6 |          5 |   01/07/2014 |
|  4 |    30 |   1 |         3 |        2 |          3 |   01/08/2014 |
|  5 |    30 |   0 |         3 |        4 |          3 |   01/09/2014 |
|  6 |    30 |   0 |         0 |        0 |          0 |   01/10/2014 |
|  7 |    30 |   0 |         0 |        0 |          0 |   01/11/2014 |
1

Сообщение об ошибке само по себе очень явно. Вы не можете поставить колонки без применения агрегатной функции к нему в SELECTстатье , если он не является частью GROUP BY. И причина очень проста SQL Server не знает , какое значение для этого столбца в группу вы хотите выбрать. Это не детерминированным и поэтому запрещены.

Вы можете поместить все столбцы , кроме Idв GROUP BYи использовать MIN()или MAX()на Idили вы можете использовать оконную функцию ROW_NUMBER()следующим образом

SELECT Id, Total, New, Completed, Assigned, Unassigned, CONVERT(VARCHAR(10), CreatedDtUTC,101) CreatedDtUTC
  FROM
(
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Total, New, Completed, Assigned, Unassigned, CreatedDtUTC 
                                 ORDER BY id DESC) rnum
    FROM TblUsage t
) q
 WHERE rnum = 1

Выход:

| ID | ИТОГО | NEW | ЗАВЕРШЕНА | НАЗНАЧЕН | НЕРАСПРЕДЕЛЕННЫЕ | CREATEDDTUTC |
| ---- | ------- | ----- | ----------- | ---------- | ------- ----- | -------------- |
| 3 | 29 | 1 | 5 | 6 | 5 | 01/07/2014 |
| 6 | 30 | 0 | 0 | 0 | 0 | 01/10/2014 |
| 7 | 30 | 0 | 0 | 0 | 0 | 01/11/2014 |
| 5 | 30 | 0 | 3 | 4 | 3 | 01/09/2014 |
| 4 | 30 | 1 | 3 | 2 | 3 | 01/08/2014 |

Вот SQLFiddle демо