Link to home
Start Free TrialLog in
Avatar of nutnut
nutnutFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Count from Multiple Tables

Hi

What is the fastest way to extract count information from multiple tables and return as individual columns.  So for each company I want to see the

count of client from dbo.Client
count of items sold from dbo.Sold
count of item purchased from dbo.Purchased  

The Company column is available in all 3 tables.

Results to have 4 columns

Company
Count of Clients
Count of Sold
Count of  Purchased

Would a pivot work best and if so what would be the SQL?

thansk

Andrew
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Not sure if this is the best way (probably not), but an easy way you could do it is:
SELECT SUM(CASE WHEN Col=1 THEN Tot ELSE 0 END) Col1,SUM(CASE WHEN Col=2 THEN Tot ELSE 0 END) Col2,SUM(CASE WHEN Col=3 THEN Tot ELSE 0 END) Col3
FROM (
SELECT 1 Col,COUNT(*) Tot FROM Clients
UNION ALL
SELECT 2,COUNT(*) FROM Sold
UNION ALL
SELECT 3,COUNT(*) FROM Purchased) t
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select a.company, a.client_count, b.sold_count, c.purchased_count from
(select company, count(*) as client_count from client group by company) as a,
(select company, count(*) as sold_count from sold group by company) as b,
(select company, count(*) as purchased_count from purchased group by company) as c
where a.company = b.company and b.company = c.company;
Avatar of nutnut

ASKER

Thanks worked perfect
Interesting, you never indicated there was a company table with foreign keys in the other tables.
Avatar of nutnut

ASKER

"The Company column is available in all 3 tables."
I know, which is why my query only uses those 3 tables, but your accepted query uses another table, company, that somehow ties to the 3 tables via some foreign key relationship. Does that company table actually exist? If not, then the accepted solution won't work and, if so, there is still no need to join to it.