Help with a SELECT statement using GROUP and SUM

  • [font="Courier New"]Given a table of transaction consisting of the following columns:

    CardType nvarchar(50)

    Completed bit

    I'm looking for SQL statement which would present detail line to include:

    o Each unique CardType and

    o A count of which ones had Completed = TRUE and

    o A final column containing the SUM of all the Completed count

    Note, the Total value will be the same for each detail line because its value is the SUM of all the counts in the Completed column.

    Card Type Completed Total

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

    ATM_Issue.CRD 15 600

    ATM_RePIN.CRD 3 600

    Debit_Business_Issue.CRD 10 600

    Debit_Business_RePIN.CRD 10 600

    Debit_Issue.CRD 355 600

    Debit_RePIN.CRD 41 600

    NE_ATM_Issue.CRD 20 600

    NE_Debit_Business_Issue.CRD 30 600

    NE_Debit_Business_RePIN.CRD 16 600

    NE_Debit_Issue.CRD 40 600

    Pinnacle_Debit_Issue.CRD 60 600[/font]

  • Hi and welcome to SSC. In order for somebody to offer much help we need some help from you first. If you can post ddl (create table statements), sample data (insert statements) and desired output based on your sample data it will make sure we are all on the same page. Take a look at the first link in my signature for best practices on posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • declare @MyTable table (CardType varchar(50), Completed bit)

    insert into @MyTable

    VALUES

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 0),

    ('ATM_RePIN.CRD' ,1),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0)

    select CardType, SUM(Convert(int,Completed)) as Completed, COUNT(*) as Total

    from @MyTable

    group by CardType

  • Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).

    Any additional ideas?

    CardType Completed Total

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

    ATM_Issue.CRD 5 6

    ATM_RePIN.CRD 1 6

    Debit_Business_Issue.CRD 4 6

    Debit_Business_RePIN.CRD 2 6

  • raisleger (1/25/2012)


    Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).

    Any additional ideas?

    CardType Completed Total

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

    ATM_Issue.CRD 5 6

    ATM_RePIN.CRD 1 6

    Debit_Business_Issue.CRD 4 6

    Debit_Business_RePIN.CRD 2 6

    What you are describing is a running total. Jeff wrote a great article on this topic. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • how about this...

    DECLARE @mytable AS TABLE(CardType nvarchar(50),Completed BIT)

    insert into @MyTable

    VALUES

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 0),

    ('ATM_RePIN.CRD' ,1),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0)

    --SELECT * FROM @mytable

    SELECT cardtype,COUNT(completed) AS cardtypecount,(SELECT COUNT(*) FROM @mytable WHERE completed = 1)

    FROM @mytable

    WHERE completed = 1

    GROUP BY cardtype

  • Yes that produced the desired results...Thanks.

  • Sean Lange (1/25/2012)


    raisleger (1/25/2012)


    Pretty close, but the "Total" column is incorrectly valued with "6" versus the expected "12" (5 + 1 + 4 + 2).

    Any additional ideas?

    CardType Completed Total

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

    ATM_Issue.CRD 5 6

    ATM_RePIN.CRD 1 6

    Debit_Business_Issue.CRD 4 6

    Debit_Business_RePIN.CRD 2 6

    What you are describing is a running total. Jeff wrote a great article on this topic. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

    Nope... not a running total in this case. It looks like it because of the low number of rows but the total doesn't increment on each row. It's a simple sum by group on each row.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DECLARE @Example AS TABLE

    (

    CardType nvarchar(50) NOT NULL,

    Completed bit NOT NULL

    );

    INSERT @Example

    (CardType, Completed)

    VALUES

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 0),

    ('Debit_Business_RePIN.CRD', 1),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 0),

    ('ATM_RePIN.CRD' ,1),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0),

    ('ATM_Issue.CRD', 1),

    ('ATM_RePIN.CRD' ,0),

    ('Debit_Business_Issue.CRD', 1),

    ('Debit_Business_RePIN.CRD', 0);

    SELECT

    e.CardType,

    Completed = COUNT_BIG(*),

    Total = SUM(COUNT_BIG(*)) OVER ()

    FROM @Example AS e

    WHERE

    e.Completed = 1

    GROUP BY

    e.CardType

    ORDER BY

    e.CardType;

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

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