# Thread: Analyze 2 Columns in 1 Table

1. Registered 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

thnx

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered 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)

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered 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 null

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered 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 time

#### Posting Permissions

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