A case with GROUP BY

Question

Post reply

A case with GROUP BY

Dinendra Nath Dolai

Mr or Mrs. 500

Points: 594
More actions
September 28, 2008 at 10:31 am

#193751

Hi,
First I want to thank everybody in this forum. I always learn new things here.
I am facing a problem with grouping of data with GROUP BY clause.
I have a table with the following data structure which is used to hold sales data.
ItemSales(SaleDateTime,ItemCode)
Sample data for ItemSales
SaleDateTime ItemCode
27 Sept 2008 09:30:00 1
27 Sept 2008 09:45:00 1
28 Sept 2008 10:10:00 1
28 Sept 2008 11:45:00 1
28 Sept 2008 13:17:00 1
28 Sept 2008 14:50:00 1
Now what I want is how many items were sold each day
That is the output for the above data will be
SaleDate SaleCount
27 Sept 2008 2
28 Sept 2008 4
Actually I need to perform group by only on the Date portion of a datetime field.
Can someone please help me to carryout this?
Thanks in advance.
Dinendra

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

Try this:

SSET DATEFORMAT DMY;

SET NOCOUNT ON;

DECLARE @t TABLE (SaleDate DATETIME, ItemCode INT)

INSERT INTO @t (SaleDate, ItemCode) SELECT '27-09-2008 09:30:00',1

INSERT INTO @t (SaleDate, ItemCode) SELECT '27-09-2008 09:45:00',1

INSERT INTO @t (SaleDate, ItemCode) SELECT '28-09-2008 10:10:00',1

INSERT INTO @t (SaleDate, ItemCode) SELECT '28-09-2008 11:45:00',1

INSERT INTO @t (SaleDate, ItemCode) SELECT '28-09-2008 13:17:00',1

INSERT INTO @t (SaleDate, ItemCode) SELECT '28-09-2008 14:50:00',1

SELECT

DATEADD(d, 0, DATEDIFF(d, 0, SaleDate) ) AS SaleDate,

COUNT(ItemCode) AS Sales

FROM @t

GROUP BY DATEADD(d, 0, DATEDIFF(d, 0, SaleDate) )

/*

SaleDate Sales

----------------------- -----------

2008-09-27 00:00:00.000 2

2008-09-28 00:00:00.000 4

*/

.

David Korb SSCrazy Points: 2505 More actions · Answer 2

Why do summersaults with the date like that? The convert() function works well. One of my favorite uses is for the 'YYYYMMDD' output using 112 as the input "style" argument. See BOL for other output formats.

select

convert( varchar(8), date_column, 112 )

,count(type_column)

from table_name

group by convert( varchar(8), date_column, 112 )

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

There was a mistake in my original query and I have corrected it. "DATEADD(d, 0, DATEDIFF(d, 0, SaleDate) )" will remove the TIME portion from a DATETIME value and will be much more efficient than a CONVERT or CAST.

.

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Sprocking,

the use of DATEADD and DATEDIFF functions looks a bit strange at first, but is more efficient - and also more flexible. Once you learn how it works, you may modify it easily to discard only minutes or seconds, to get first day of next month, or the date of next Friday and many similar things. I like this flexibility and that's why I prefer DATEADD - but of course CONVERT can be used as well. On small tables the difference in perfomance is negligible.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SprocKing (9/29/2008)
Why do summersaults with the date like that? The convert() function works well. One of my favorite uses is for the 'YYYYMMDD' output using 112 as the input "style" argument. See BOL for other output formats.
select
convert( varchar(8), date_column, 112 )
,count(type_column)
from table_name
group by convert( varchar(8), date_column, 112 )

Why cast a value as VARCHAR when it's always going to have the same number of characters?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Korb SSCrazy Points: 2505 More actions · Answer 6

Vladan (9/29/2008)
Sprocking,
the use of DATEADD and DATEDIFF functions looks a bit strange at first, but is more efficient - and also more flexible. Once you learn how it works, you may modify it easily to discard only minutes or seconds, to get first day of next month, or the date of next Friday and many similar things. I like this flexibility and that's why I prefer DATEADD - but of course CONVERT can be used as well. On small tables the difference in perfomance is negligible.

Thanks! I'll put in my research queue.