Analyze 2 Columns in 1 Table

vextout · #1 (**permalink**) 07-22-04, 14:21

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

Pat Phelan · #2 (**permalink**) 07-22-04, 15:23

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

vextout · #3 (**permalink**) 07-22-04, 15:59

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)

Pat Phelan · #4 (**permalink**) 07-22-04, 16:16

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

vextout · #5 (**permalink**) 07-22-04, 16:56

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

Pat Phelan · #6 (**permalink**) 07-22-04, 23:37

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

vextout · #7 (**permalink**) 07-23-04, 09:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech