Need to count the number of orders placed in one year.

Question

Post reply

Need to count the number of orders placed in one year.

sanjeev_krs2004

Default port

Points: 1462
More actions
June 23, 2009 at 7:18 am

#135385

HI -

I have one question where I need to count the number of orders placed for a particular item.

For example:

Item Date

================================================

a 2008-01-01 00:00:00.000

a 2008-01-01 00:00:00.000

b 2008-01-01 00:00:00.000

c 2008-01-01 00:00:00.000

c 2009-01-01 00:00:00.000

c 2009-01-01 00:00:00.000

c 2009-01-01 00:00:00.000

b 2009-01-01 00:00:00.000

d 2009-01-01 00:00:00.000

d 2009-01-01 00:00:00.000

Now I want result something like:

2008 2009

====================

a 2 0

b 1 1

c 1 3

d 0 2

Please let me know if I am not clear here.

Thanks in advance..!!

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

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

Tom257 SSChampion Points: 11475 More actions · Answer 1

Tom257

SSChampion

Points: 11475

June 23, 2009 at 7:52 am

#1013936

You could use something like

SELECT

COUNT(ITEM) OVER (PARTITION by Date) AS Count, Date

FROM Table

See the OVER() clause in BOL

you can probably use an expression like DATEPART(YEAR,Date) in the PARTITION bit

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Adam Bean

One Orange Chip

Points: 26631

June 23, 2009 at 7:55 am

#1013943

Response after mine was better 🙂

t.hitendra SSCommitted Points: 1620 More actions · Answer 3

Hi there,

I have tried to solve your problem using row_number() and pivot function.

create table #tblItems(ItemName char(1),Date datetime)

insert into #tblItems

select 'a','2008-01-01 00:00:00.000' UNION ALL

select 'a','2008-01-01 00:00:00.000' UNION ALL

select 'b','2008-01-01 00:00:00.000' UNION ALL

select 'c','2008-01-01 00:00:00.000' UNION ALL

select 'c','2009-01-01 00:00:00.000' UNION ALL

select 'c','2009-01-01 00:00:00.000' UNION ALL

select 'c','2009-01-01 00:00:00.000' UNION ALL

select 'b','2009-01-01 00:00:00.000' UNION ALL

select 'd','2009-01-01 00:00:00.000' UNION ALL

select 'd','2009-01-01 00:00:00.000';

select ItemName,[2008] ,[2009]

from

(

select ItemName,year(Date) as Date

,count(ItemName) over (partition by Date,ItemName) as CntItem

from #tblItems

) P

Pivot( Count(CntItem) For Date in ([2008],[2009])

) as pvt

drop table #tblItems

Check it out.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442447

June 23, 2009 at 8:07 am

#1013958

Here is another solution that I think is a bit simplier, but since I seem to be having a problem (again) posting the code directly in the thread, it is attached as a text file.