Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Analyze 2 Columns in 1 Table

    we have about 50 columns in 1 table but only want to analyze 2 of them

    column 1 = Source
    source is dynamic as far as the contents are concern
    source
    ======
    h1
    h2
    t1
    t2
    DM
    PS

    column 2 = trans
    trans can only have 5 different values as the last value
    trans
    ======
    R
    RRR
    RRRRR
    N
    U
    X
    E
    NRR

    so i would analyze the right(trans,1) = 'whatever'


    I need a query - hopefully 1 liner - that can analyze the count of source by trans so the outcome would look something lke this with totals if possble in the string , if not fine

    t_src N R X U E total
    h1 0 31 1 0 14 46
    h2 0 43 2 0 12 57
    t1 0 33 4 0 8 45
    t2 0 11 3 0 9 23
    dm 64 12 0 6 0 82
    ps 32 4 1 5 0 42
    total 96 134 11 11 43 295

    where the left(trans,1) becomes the column name header and the t_src becomes the row header

    thnx
    Beyond Limitation

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT t_src
    ,  Sum(CASE WHEN 'N' = Left(trans, 1) THEN 1 END) AS N
    ,  Sum(CASE WHEN 'R' = Left(trans, 1) THEN 1 END) AS R
    ,  Sum(CASE WHEN 'X' = Left(trans, 1) THEN 1 END) AS X
    ,  Sum(CASE WHEN 'U' = Left(trans, 1) THEN 1 END) AS U
    ,  Sum(CASE WHEN 'E' = Left(trans, 1) THEN 1 END) AS E
    ,  Count(*) AS 'total'
       FROM theTable
       GROUP BY t_src
    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    thanx
    i didn't realize it was that simple.
    Is there any way to get the totals at the bottom with the query you gave me

    or should i just use another query(which is fine by me)
    Beyond Limitation

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are ways, but they aren't really pretty. I'd just use the same query and a UNION ALL of that query using a constant like 'All' for the src_id but without the GROUP BY to get the footer totals.

    -PatP

  5. #5
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    i got this - total works , but the total is on top -- how can I make it become a footer
    SELECT '' as 'Total'
    SELECT '' shows blank under the t_src column

    SELECT t_src
    , Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) AS 'New'
    , Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
    , Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
    , Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
    , Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
    , Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
    , Count(*) AS 'Total'
    FROM theTABLE where t_src is not null group by t_Src
    union
    SELECT '' as 'Total'
    , Sum(CASE WHEN 'N' = right(hftr, 1) THEN 1 END) as 'New'
    , Sum(CASE WHEN 'R' = right(hftr, 1) THEN 1 END) AS 'Renew'
    , Sum(CASE WHEN 'U' = right(hftr, 1) THEN 1 END) AS 'Address Change'
    , Sum(CASE WHEN 'X' = right(hftr, 1) THEN 1 END) AS 'Cancel'
    , Sum(CASE WHEN 'E' = right(hftr, 1) THEN 1 END) AS 'Email List Removal'
    , Sum(CASE WHEN right(hftr,1) <> 'N' AND right(hftr,1) <> 'R' AND right(hftr,1) <> 'X' AND right(hftr,1) <> 'U' AND right(hftr,1) <> 'E' THEN 1 END ) AS 'Other'
    , Count(*) AS 'Total'
    FROM theTABLE where t_src is not null
    Beyond Limitation

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, time to get out the "big hammer" and get fiesty!
    Code:
    SELECT t_src
    ,  Sum(CASE WHEN 'N' = foo THEN 1 END) AS 'New'
    ,  Sum(CASE WHEN 'R' = foo THEN 1 END) AS 'Renew'
    ,  Sum(CASE WHEN 'X' = foo THEN 1 END) AS 'Cancel'
    ,  Sum(CASE WHEN 'U' = foo THEN 1 END) AS 'Address Change'
    ,  Sum(CASE WHEN 'E' = foo THEN 1 END) AS 'Email List Removal'
    ,  Sum(CASE WHEN foo NOT IN ('N', 'R', 'X', 'U', 'E') THEN 1 END) AS Other
    ,  Count(*) AS 'total'
       FROM (
          SELECT t_src, Right(hftr, 1) AS foo, 1 AS bar
             FROM theTable
          UNION ALL SELECT '', Right(hftr, 1), 2
             FROM theTable) AS a 
       WHERE t_src IS NOT NULL
       GROUP BY t_src, bar
       ORDER BY bar
    -PatP
    Last edited by Pat Phelan; 07-23-04 at 09:42. Reason: Added NOT NULL test and literal column names

  7. #7
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    i get it
    the group by bar allows the total to become a footer
    I understand the code
    Thank you for all your help and time
    Beyond Limitation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •