CODEWITHSUNDEEP
sql-servert-sqlgroup-by
Prince Jea
Prince Jea

Reputation: 5690

Group by date in SQL Server

I'm having a problem with grouping in SQL Server.

I have a datetime column but I want to group only by date,

Here's my code

SELECT ProductCode,
       ProductName,
       ProductType,
       UnitPrice,QTY,
       Amount,
       (convert(varchar, TransactionDate, 101) )AS sTransactionDate
FROM DailyTransactions 
GROUP BY TransactionDate, ProductCode,
          ProductName,ProductType,UnitPrice,QTY,Amount

RESULT:

2/17/2012 appears three times because it has different times ...

Upvotes: 4

Views: 12798

Answers (5)

nealkernohan
nealkernohan

Reputation: 836

Simply put, your GROUP BY must appear somewhere in your select. You are already converting TransactionDate to a 'date only' string, though DATEADD and DATEDIFF together are actually more efficient.

Hence, simply GROUP BY the same format:

SELECT ProductCode,
       ProductName,
       ProductType,
       UnitPrice,QTY,
       Amount,
       DATEADD(day, DATEDIFF(day, 0, TransactionDate), 0) AS sTransactionDate
FROM DailyTransactions 
GROUP BY DATEADD(day, DATEDIFF(day, 0, TransactionDate), 0), ProductCode,
          ProductName,ProductType,UnitPrice,QTY,Amount

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280590

Try using CHAR(10) instead of varchar when applying your convert. You shouldn't ever declare char/varchar without length. In this case you aren't truncating enough characters because this is a varchar(30) whereas you really only care about the first 10 characters. Also, your group by should include an identical convert.

Upvotes: 1

John Woo
John Woo

Reputation: 263893

Since you want to display only one result, why don't you use DISTINCT?

SELECT DISTINCT ProductCode, ...
FROM ...
GROUP BY ProductCode

Upvotes: 2

Bruno Silva
Bruno Silva

Reputation: 3097

This should be a quick way of grouping your results by date:

SELECT  
   ProductCode,ProductName,ProductType,UnitPrice,QTY,Amount,(convert(varchar, TransactionDate, 101) )AS sTransactionDate
   FROM DailyTransactions 
   GROUP BY DATEADD(day, DATEDIFF(day, 0, TransactionDate), 0), ProductCode,ProductName,ProductType,UnitPrice,QTY,Amount

In this case we are getting the difference between the start of the day and the time in the date and then removing it.

Upvotes: 2

John Pick
John Pick

Reputation: 5650

You could try:

GROUP BY
  DATEPART(year, TransactionDate),
  DATEPART(month, TransactionDate),
  DATEPART(day, TransactionDate)

Upvotes: 2

Related Questions