Sum, Count and Group by in the same query

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)

Sum, Count and Group by in the same query

Author

Topic

Gil_Bar
Starting Member

11 Posts

Posted - 2008-04-16 : 03:17:22

Hi,
I have two tables in my DB:
tbl_Users: callSign(Char), FirstName(Char), LastName(Char)
tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.

The result that I want to see is:

CALL SIGN Last Name First Name Date Number Of Events TotalKM
111 MR. X 01/01/2008 3 40

I know this:
The number of events is countable.
if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.

But how do I group it together?!

Thanks,
Gil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 03:52:35

[code]SELECT u.callSign,u.Last Name,u.First Name, e.EventDate AS Date, t.[Number Of Events],e.TotalKM
FROM tbl_Users u
INNER JOIN tbl_Events e
ON e.callSign=u.callSign
INNER JOIN (SELECT callSign,SUM(Convert(int,TotalKM)) AS .[Number Of Events]
FROM tbl_Events
GROUP BY callSign)t
ON t.callSign=e.callSign[/code]

Gil_Bar
Starting Member

11 Posts

Posted - 2008-04-16 : 04:21:37

visakh16,

Thank you for your fast reply!

It's working well.

Gil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources