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

thnxI'd use: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|||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)|||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|||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|||Ok, time to get out the "big hammer" and get fiesty!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|||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