Link to home
Create AccountLog in
Avatar of Whah
Whah

asked on

MSSQL Count and Sum

MSSQL 2000

I have 6 tables.  I'd like to get a total rowcount from each table and then sum all the rowcounts.

I'm starting with the following:
select 'APPLICANT', count(*) from ae_dt21
union all
select 'FSS', count(*) from ae_dt11
union all
select 'HOUSING_MANAGEMENT', count(*) from ae_dt2
union all
select 'LANDLORD', count(*) from ae_dt8
union all
select 'PERSONNEL', count(*) from ae_dt15
union all
select 'RESIDENT_SERVICES', count(*) from ae_dt14

Desired results:
APPLICANT|34939
FSS|3199
HOUSING_MANAGEMENT|79164
LANDLORD|2584
PERSONNEL|1263
RESIDENT_SERVICES|256
TOTAL|121405
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

You should be able to pull this off by putting your query into a temp table, then querying off of that.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
      DROP TABLE #tmp
      
CREATE TABLE #tmp (table_name varchar(30), row_count int)

INSERT INTO #tmp (table_name, row_count)
select 'APPLICANT', count(*) from ae_dt21
union all
select 'FSS', count(*) from ae_dt11
union all
select 'HOUSING_MANAGEMENT', count(*) from ae_dt2
union all
select 'LANDLORD', count(*) from ae_dt8
union all
select 'PERSONNEL', count(*) from ae_dt15
union all
select 'RESIDENT_SERVICES', count(*) from ae_dt14

SELECT table_name, row_count
FROM #tmp
UNION
SELECT 'ALL' as table_name, SUM(row_count) as row_count
FROM #tmp
ASKER CERTIFIED SOLUTION
Avatar of Steve Dubyo
Steve Dubyo
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
in mssql server, you can do like this.

;with data as ( 
select 'APPLICANT' table_name, count(*) cnt from ae_dt21
union all
select 'FSS', count(*) from ae_dt11
union all
select 'HOUSING_MANAGEMENT', count(*) from ae_dt2
union all
select 'LANDLORD', count(*) from ae_dt8
union all
select 'PERSONNEL', count(*) from ae_dt15
union all
select 'RESIDENT_SERVICES', count(*) from ae_dt14
)
select table_name, cnt from data
UNION ALL
select 'TOTAL', sum(cnt) cnt from data

Open in new window

Any reason you chose the answer you did for all points?
As far as I can tell all three answers are correct.
Avatar of Whah
Whah

ASKER

That was the one I ended up using so I didn't test the others.  I can reallocate if you feel this is an inappropriate way to apply points.