×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Display results of 3 rows in just 1

Display results of 3 rows in just 1

Display results of 3 rows in just 1

(OP)
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

RE: Display results of 3 rows in just 1

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

RE: Display results of 3 rows in just 1

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close