Results 1 to 7 of 7
Thread: Analyze 2 Columns in 1 Table

072204, 15:21 #1Registered User
 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
thnxBeyond Limitation

072204, 16:23 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54I'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

072204, 16:59 #3Registered User
 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

072204, 17:16 #4Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54There 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

072204, 17:56 #5Registered User
 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 nullBeyond Limitation

072304, 00:37 #6Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Ok, 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
Last edited by Pat Phelan; 072304 at 09:42. Reason: Added NOT NULL test and literal column names

072304, 10:59 #7Registered User
 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 timeBeyond Limitation