Post reply

summing totals in a query

  • 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?

     

  • February 26, 2004 at 4:35 pm

    #496047

    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

  • February 26, 2004 at 5:26 pm

    #496059

    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

  • February 26, 2004 at 7:10 pm

    #496068

    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

  • February 26, 2004 at 7:32 pm

    #496076

    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?

     

     

  • February 26, 2004 at 7:53 pm

    #496079

    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

  • February 27, 2004 at 5:38 am

    #496151

    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

  • February 27, 2004 at 6:51 am

    #496178

    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.

  • February 27, 2004 at 8:28 am

    #496218

    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.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • March 3, 2004 at 8:41 am

    #496951

    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

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

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