Multiple count in a query - 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

General SQL Server Forums

New to SQL Server Programming

Multiple count in a query

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-18 : 18:28:55

How do i get multiple counts in a query? Please your help

I run the following simple query to pull the records given below :

select case_id, dateadd (hh,-7,dateAdd(ss, date, '19700101'))[Date],
type, symbol from records

raw data
case_id Date type symbol
123 10/18/2009 A X
456 10/18/2009 A Y
789 10/20/2009 A Z
987 10/20/2009 B X
654 10/20/2009 A X
321 10/21/2009 B X

Expected Results

Date symbol type A type B
Count Count
10/18/2009 X 1 0
10/18/2009 Y 1 0
10/18/2009 Z 0 0
10/20/2009 X 1 1
10/20/2009 Y 0 0
10/20/2009 Z 1 0
10/21/2009 X 0 1
10/21/2009 Y 0 0
10/21/2009 Z 0 0

Cheerz!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-18 : 19:20:39

what do you want to count ?

Also the expected result and raw data does not seems to match.

KH
[spoiler]Time is always against us[/spoiler]

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-19 : 06:22:27

Hi

I hope the example below makes mores sense

Inventory # Date Vehicle_Type Fuel_type
1 18/10/2009 Car Petrol
3 18/10/2009 Bus Diesel
5 18/10/2009 Bus Petrol
6 18/10/2009 Bus Hybrid
9 18/10/2009 Bus Petrol
10 18/10/2009 Car Diesel

Expected Result :

Date Vehicle_Type Count_Cars Count_Bus
18/10/2009 Petrol 1 2
18/10/2009 Diesel 1 1
18/10/2009 Hybrid 0 1

This example has data for just one day. But the actual data will have data for more than one day. So it should show the counts date after date.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2009-10-19 : 06:44:51

SELECT * FROM
( SELECT
date,
fuel_type,
vehicle_type
FROM dbo.testTable --YOURTABEL NAME
) AS abc
PIVOT
(
COUNT(vehicle_type)
FOR vehicle_type IN([CAR],[BUS])
) AS Pvt

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-19 : 07:10:32

Hey thanks for the reply.
i use the following conversion for date
dateadd (hh,-7,dateAdd(ss, date, '19700101'))[Date]

which make the output to show like the one given below :
2009-10-05 14:40:52.000

Since, the time part is also included in this conversion, the pivot table created takes each timestamp as a different entry. How do I take only the date part so that i get the counts for the day

s_anr
Yak Posting Veteran

81 Posts

Posted - 2009-10-19 : 15:49:30

i figured it out.. thanks..:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources