Display results of 3 rows in just 1

[cymerman]

I have a table with canc_code1 canc_code2 canc_code3. These are cancellation reasons.
There is a cancel codes table and a m_cancellations table.

cancel code TABLE
canc_code canc_desc
01 did not want
02 i do not have money

m_cancellations TABLE
contract canc_code1 canc_code2 canc_code3
10000 01 02 03
20000 02
30000 02

My query is:
select canc_code1, canc_code2, canc_code3, count(*) from m_cancellations
group by 1, 2, 3;

How do I display distinct canc_codes used by users on these 3 fields in just one result collum and also count how many of each has in the DB ?

I need something like this

canc_code totals
01 1522
02 1221
03 452

THANKS A LOT

 

[PHV]

The problem is that your m_cancellations table is not normalised.
You may try this:
SELECT canc_code, COUNT(*) totals
FROM (
SELECT canc_code1 canc_code FROM m_cancellations
UNION ALL SELECT canc_code2 FROM m_cancellations
UNION ALL SELECT canc_code3 FROM m_cancellations
)
GROUP BY 1

If your version of informix doesn't like this syntax then use a temporary table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cymerman]

if i use just

SELECT distinct canc_code1 canc_code FROM m_cancellations
UNION ALL SELECT canc_code2 FROM m_cancellations
UNION ALL SELECT canc_code3 FROM m_cancellations
grOUP BY 1

works. but if i use your complete statement i receive syntax error.
can i get the total count on each of the canc codes using the same query?