Re: Null in SQL select union

[rob@xxxxxxxxx]

Great explanation this time!

So if I have this:
select ctype, color
from rob/titus 
....+....1....+....2.
CTYPE       COLOR 
Pickup      Red 
Pickup      Red 
Pickup      Blue 
Pickup      Blue 
Pickup      Blue 
Van         Red 
Van         Red 
Van         Blue 
Van         Green 
Van         Green 
Van         Green 
********  End of data

And I try this:
select ctype, color, count(*)
from rob/titus 
group by ctype, color 
union 
select ctype, ' ',  count(*) 
from rob/titus 
group by ctype 
order by ctype, 2 
....+....1....+....2....+....3....+...
CTYPE       COLOR         COUNT ( * ) 
Pickup                              5 
Pickup      Blue                    3 
Pickup      Red                     2 
Van                                 6 
Van         Blue                    1 
Van         Green                   3 
Van         Red                     2 
********  End of data  ******** 

However you want the itermediate totals following the details, not prior.

How's about changing your user's perspective?  In the PC/GUI land you 
often see the totals first.  And then you drill down into the details.

Customer:  You got any pickups?
Rep:  Sure we have 5 of them, three of them are blue and two are red.  Let 
me show you...


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Titus Kruse" <t_kruse@xxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/26/2004 03:21 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Null in SQL select union







----- Original Message -----
From: <rob@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 25, 2004 5:05 PM
Subject: Re: Null in SQL select union


> I'm a little confused also.  Suppose your table had the following data:
> ....+....1....+....2....+.

I want to get one line for each car color and type and an additional line
with the number of cars with the same type in my query output:

Type    Color    Number
--------------------------------
Pickup    Red    -
Pickup    Blue    -
-            -            2
Van        Red    -
Van        Blue    -
Van        Green    -
-            -            3

When I use value 0 or '' as replacement in the second select-clause, the
total line are not positioned after the detail lines. Using NULL should do
so.

Hope I could make clear what I mean? :-)

Titus



_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.