How top Sum values of two columns using Select Query

[puregoldeneye]

Hi all,

I have a question: How to Sum values of two columns (car_count) using Select Query?

**Direction: A road has two lanes, direction 1, and direction 2. I want the output to be only one row per hour, with the sum of car_count values showing up.

For Example:--

hour station_id speed_class car_count direction
---- ----------- ----------- ----------- ---------
8AM 5001 1 10 2
8AM 5001 1 10 1
8AM 5001 2 500 1
8AM 5001 2 20 2
8AM 5001 3 25 2
8AM 5001 3 800 1
8AM 5001 4 20 2
8AM 5001 4 12 1

I want to have the select query out put to be:--

hour station_id speed_class car_count
---- ----------- ----------- -----------
8AM 5001 1 20
8AM 5001 2 520
8AM 5001 3 825
8AM 5001 4 32

My Select statement is:--

select
cast(((DatePart(Hour,date_hour)-1) % 12)+ 1 as varchar(2)) + CASE when DatePart(Hour,date_hour)<12 then 'AM' else 'PM' end AS hour, station_id, speed_class, car_count, direction
from
speed_class_count

where
date_hour >='2003-04-04 08:00:00.000' and
date_hour <= '2003-04-04 20:00:00.00' and station_id= '5001' and
speed_class > '0'

order by
date_hour, speed_class

Help!

 

[swampBoogie]

select
cast(((DatePart(Hour,date_hour)-1) % 12)+ 1  as varchar(2))  + CASE when DatePart(Hour,date_hour)<12 then 'AM' else 'PM' end AS hour, station_id, speed_class, sum(car_count) 
from
speed_class_count

where
date_hour >='2003-04-04 08:00:00.000' and
date_hour <= '2003-04-04 20:00:00.00' and station_id= '5001' and
speed_class > '0'
group by
cast(((DatePart(Hour,date_hour)-1) % 12)+ 1  as varchar(2))  + CASE when DatePart(Hour,date_hour)<12 then 'AM' else 'PM' end AS hour, station_id, speed_class
order by hour, speed_class

 

[puregoldeneye]

As I understand, we sum the column car_count, and group the fields!

I tried your query, and I want to say Thank You! It works, and gives the desired result.

If it is not too much trouble, would you suggest me any REALLY good SQL Server Programming Books?

Have a nice day!

 

[JamesLean]

Please see the following FAQ:

Useful Reference Books for SQL Server Professionals
faq183-3324

--James