MS SQL query - Count and Union

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

MS SQL query - Count and Union

Author

Topic

viallos
Starting Member

3 Posts

Posted - 2011-10-19 : 19:05:03

Folks, I'm looking for help with Count query

I have 2 tables

Table1 - 3 columns (Staff)

SID | FName | SName
11 | Joe1 | Blog1
22 | Joe2 | Blog2
33 | Joe3 | Blog3

Table2 - 4 columns (Transactions)

TID | Tool | SHire | SReturn
1 | Tool1 | 11 | 33
2 | Tool2 | 22 | 11
3 | Tool2 | 11 | 22
4 | Tool1 | 33 | 22

Table1 contains customer service staff who either rent or take back tools that are for rent and transaction is recorded in Table2.
Table2 contain transactions - what was rented and who from staff rented as well who took it back.
I would like to count number of contacts made by each staff with customer (rent or take back) within single query.

Expected result for above would be

SID | Contacts
11 | 3
22 | 3
33 | 2

Next step would be to add FName and SName into results.

So far I have following


select SHire , count(SHire)
from Table2
group by SHire 
union all
select THire , count(THire)
from Table2
group by SHire 
order by 1

which gives me results separeted for each staff by action

SID | Contacts
11 | 2
11 | 1
22 | 1
22 | 2
33 | 1
33 | 1

Any ideas?

Thanks

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-19 : 23:35:53

Try to JOIN the two instances Table1 to Table2, joining on SHire SReturn, then group all the columns in table Table1 and run a count()

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:21:49

[code]
select Val,COUNT(*) AS Contacts
from Table2
UNPIVOT(Val FOR Category IN ([SHire],[SReturn]))u
GROUP BY Val,Category
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources