summing totals in a query

Question

Post reply

summing totals in a query

deek

SSCommitted

Points: 1886
More actions
February 26, 2004 at 4:23 pm

#84300

i have a query which counts the # of records in several tables; but i need it to also return the sum of those counts as one total. i can't seem to make it work.
i'm sure this is a cinch; but i don't have time to explore it right now. anyone?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

without more details all I can do is just guess

is it like:

Select tblName , Cnt

Form (your query)

UNION ALL

SELECT 'ALL' , Count(*)

FRom (your query)

HTH

* Noel

deek SSCommitted Points: 1886 More actions · Answer 2

this is the code so far. i don't have any summing code to show you; because i can't seem to write any that works.

i need to sum up the record counts from tables A through D, then subtract that number from the record count from table E:

select count(*) from A as Count_A

select count(*) from B as Count_B

select count(*) from C as Count_C

select count(*) from D as Count_D

select count(*) from E as Count_E

noeld SSC Guru Points: 96590 More actions · Answer 3

SELECT SUM(*) as FINALVALUE

FROM

(

select count(*) as Count_A from A

UNION ALL

select count(*) as Count_B from B

UNION ALL

select count(*) as Count_C from C

UNION ALL

select count(*) as Count_D from D

UNION ALL

select -1*count(*) as Count_E from E

) T

* Noel

deek SSCommitted Points: 1886 More actions · Answer 4

SQL doesn't like the line:

SELECT SUM(*) as FINALVALUE

and the line :

select -1*count(*) as Count_E from E

returns the negative of the count of E

was that a typo?

noeld SSC Guru Points: 96590 More actions · Answer 5

ok can you try to change

SUM(*) for SUM (T.Count_A)

the last line is Not a typo I am just alingning all counts in just one column and then After changing the sign of the count in e with a single SUM operation i can calculate what you asked for Cnt_A + Cnt_B + Cnt_C +Cnt_D -Cnt_E

* Noel

LJvanderEnt SSC Veteran Points: 206 More actions · Answer 6

Try this select statement:

Select (((select count(*) from A) + (select count(*) from B) + (select count(*) from C) + (select count(*) from D)) - (select count(*) from Klanten)) as Finalcount

cliffb SSCarpal Tunnel Points: 4598 More actions · Answer 7

Why not use variables to hold teh values then do math on the results?

DECLARE @CountA int,

@CountB int,

@CountC int,

@CountD int,

@CountE int

SET @CountA = (select count(*) from A as Count_A)

SET @CountB = (select count(*) from B as Count_B)

SET @CountC = (select count(*) from C as Count_C)

SET @CountD = (select count(*) from D as Count_D)

SET @CountE = (select count(*) from E as Count_E)

SELECT (@CountA + @CountB + @CountC + @CountD) - @COuntE AS Result

I have to say, I am a bit curious as to what you are using this calculation for.

hawg SSCertifiable Points: 6044 More actions · Answer 8

I'm not exactly sure how you are running these queries but if you are running them in a stored procedure you might also consider using a temp table. The overhead on something this small shouldn't be much and it would allow you to capture different number of counts without having to know how many variables you need.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Jeetu Newbie Points: 7 More actions · Answer 9

Well I think this might be just what you are looking for

SELECT ABS(SUM(C)) as Sum, ID FROM

(

SELECT COUNT(*) AS C, 'A' AS ID FROM A

UNION

SELECT COUNT(*) AS C, 'B' AS ID FROM B

UNION

SELECT COUNT(*) AS C, 'C' AS ID FROM C

UNION

SELECT COUNT(*) AS C, 'D' AS ID FROM D

UNION

SELECT COUNT(*) AS C, 'E' AS ID FROM E) as T

GROUP BY ID

WITH ROLLUP