Get count on a daily basis - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get count on a daily basis

Author

Topic

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 06:52:52

i have a similar problem.
there is table where datas are getting saved as
agentname saledate appno
===============================
ph0003 09/28/2009 A0010
ph0003 09/28/2009 A0011
ph0004 09/28/2009 A0012
ph0004 09/28/2009 A0013
ph0005 09/28/2009 A0014
ph0005 09/28/2009 A0015
ph0003 09/27/2009 A0002
ph0003 09/27/2009 A0003
ph0004 09/27/2009 A0004
ph0004 09/27/2009 A0005
ph0005 09/27/2009 A0006
ph0005 09/27/2009 A0007

the output should be

Agentname 09/27/2009 09/28/2009 Total
============================================
ph0003 2 2 4
ph0004 2 2 4

Can anybody tell me how to do this?????

somalia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 06:54:03

Read about PIVOT in sql server help file

Madhivanan

Failing to plan is Planning to fail

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 08:07:31

thanks madhi,

this query is returning the total count per day,

SELECT agentname,
[09/28/2009] AS sep28,
[09/26/2009] AS sep26,
[09/25/2009] AS sep25,
[09/24/2009] AS sep24
FROM
(SELECT agentname, saledate, AppNo
FROM vwTBL_MASTER_VISH) s
PIVOT
(
Count(AppNo)
FOR saledate IN ([09/28/2009],[09/26/2009],[09/25/2009],[09/24/2009])
) p
ORDER BY [agentname]

agntnm sep28 sep27 sep26 sep25

cert01 3 1 1 0
cert02 0 3 0 0
cert03 0 3 0 0
cert04 0 3 0 0
cert05 0 4 0 0
cert06 0 0 0 0
CERT07 0 0 0 0
CERT08 0 0 0 0

however my next query is:

i want the total count for each day and each agent as well like following:

agntnm sep28 sep27 sep26 sep25 total

cert01 3 1 1 0 5
cert02 0 3 0 0 3
cert03 0 3 0 0 3
cert04 0 3 0 0 3
cert05 0 4 0 0 4
cert06 0 0 0 0 0
CERT07 0 0 0 0 0
CERT08 0 0 0 0 0

total 3 14 1 0 18

can u help me in resolving this????

somalia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:09:58

Try this

select *,[09/28/2009]+[09/26/2009]+[09/25/2009]+[09/24/2009] from
(
SELECT agentname,
[09/28/2009] AS sep28,
[09/26/2009] AS sep26,
[09/25/2009] AS sep25,
[09/24/2009] AS sep24
FROM
(SELECT agentname, saledate, AppNo
FROM vwTBL_MASTER_VISH) s
PIVOT
(
Count(AppNo)
FOR saledate IN ([09/28/2009],[09/26/2009],[09/25/2009],[09/24/2009])
) p
) as t
ORDER BY [agentname]

Madhivanan

Failing to plan is Planning to fail

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 08:14:26

This is not working

,
[09/28/2009] is not a column name.Its the data in the column saledate.

somalia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:16:03

I forget to note that. It should be

select *,sep28+ sep26+ sep25+ sep24 as total from
(
SELECT agentname,
[09/28/2009] AS sep28,
[09/26/2009] AS sep26,
[09/25/2009] AS sep25,
[09/24/2009] AS sep24
FROM
(SELECT agentname, saledate, AppNo
FROM vwTBL_MASTER_VISH) s
PIVOT
(
Count(AppNo)
FOR saledate IN ([09/28/2009],[09/26/2009],[09/25/2009],[09/24/2009])
) p
) as t
ORDER BY [agentname]

Madhivanan

Failing to plan is Planning to fail

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 08:26:49

its working perfect...
but what about the last row total???
it will give the total amount of appplications per day

somalia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:32:06

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

somalia
Starting Member

10 Posts

Posted - 2009-09-29 : 08:39:36

actually i want to generate a report in excel which would consist these above details.

I want to do these sum,count jobs in backend and then fetch it in my frontend application by just giving parameters.

So,
For upto total application per agent is done.
now the part which is left is application per day which i want to incorporate within this result.

agntnm sep28 sep27 sep26 sep25 total

cert01 3 1 1 0 5
cert02 0 3 0 0 3
cert03 0 3 0 0 3
cert04 0 3 0 0 3
cert05 0 4 0 0 4
cert06 0 0 0 0 0
CERT07 0 0 0 0 0
CERT08 0 0 0 0 0

total 3 14 1 0 18

I hope I am able to make it understandable..

somalia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:45:11

[code]
SELECT [agentname],
COUNT(CASE WHEN saledate='09/28/2009' THEN AppNo ELSE NULL END) AS Sept28,
COUNT(CASE WHEN saledate='09/28/2009' THEN AppNo ELSE NULL END) AS Sept29,
..
FROM table
GROUP BY AgentName
WITH ROLLUP
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:53:06

quote:
Originally posted by somalia

actually i want to generate a report in excel which would consist these above details.

I want to do these sum,count jobs in backend and then fetch it in my frontend application by just giving parameters.

So,
For upto total application per agent is done.
now the part which is left is application per day which i want to incorporate within this result.

agntnm sep28 sep27 sep26 sep25 total

cert01 3 1 1 0 5
cert02 0 3 0 0 3
cert03 0 3 0 0 3
cert04 0 3 0 0 3
cert05 0 4 0 0 4
cert06 0 0 0 0 0
CERT07 0 0 0 0 0
CERT08 0 0 0 0 0

total 3 14 1 0 18

I hope I am able to make it understandable..

somalia