Urgent.. help with Count - 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 2000 Forums

SQL Server Development (2000)

Urgent.. help with Count

Author

Topic

john_john
Starting Member

10 Posts

Posted - 2005-06-02 : 15:49:52

This is really urgent. I'd greatly appreciate it if someone can help me out. :)

I have the following table.

Table name: Sample

Date Mode
05/31/2005 A
05/31/2005 B
05/31/2005 A
05/31/2005 A
05/30/2005 B
05/30/2005 B
05/30/2005 B

I need to build a query that accepts date as a parameter and returns the count of each type of mode for the corresponding date as well as the total count of the number of modes on that date.

So for example, the output of the query could be something like this.

Date Count-A Count-B Total_Count
05/31/2005 3 1 4
05/30/2005 0 3 3

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-02 : 16:14:47

Try this


SELECT CONVERT(varchar(10),[Date],101),
		SUM(CASE WHEN Mode = 'A' THEN 1 ELSE 0 END) AS [Count-A],
		SUM(CASE WHEN Mode = 'B' THEN 1 ELSE 0 END) AS [Count-B],
		COUNT(*) AS Total_Count
FROM YourTable
GROUP BY CONVERT(varchar(10),[Date],101)

But i am guessing this isnt what you want is it, so have a look at this article
[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

Andy

Beauty is in the eyes of the beerholder

john_john
Starting Member

10 Posts

Posted - 2005-06-02 : 16:28:35

Thanks a ton for the reply.. :) I tried it, but I get the following error...

Syntax error (missing operator) in query expression 'SUM(CASE WHEN emu.[Request-Mode] = 'Web' THEN 1 ELSE 0 END) AS [Portal]'

This is the query I'm trying to execute,

SELECT Format([Request-Date],"yyyymmdd") AS [Request Date], SUM(CASE WHEN emu.[Request-Mode] = 'Web' THEN 1 ELSE 0 END) AS [Portal]
FROM emu
GROUP BY Format([Request-Date],"yyyymmdd")
HAVING (((Format([Request-Date],"yyyymmdd"))>[Enter > start date as ccyymmdd]))
ORDER BY Format([Request-Date],"yyyymmdd");

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 16:32:16

John_john,

Are you using SQL Server? If so, Andy's query should work as is by only modifying YourTable to whatever table yours is actually named. If this is an Access question, please post your question in the Access forum as this one is for SQL Server.

Tara

john_john
Starting Member

10 Posts

Posted - 2005-06-02 : 16:35:38

Yes Tara, I'm using Access. Thanks for directing me to the proper forum.. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources