how do I UNION and GROUP BY these queries?

[karen4201]

I have two select statements, both of which return two fields of information:

select a.something as [my_something], b.otherthing as [my_otherthing] from tableA a, tableB b where a.the_date > b.the_date

and

select a.something as [my_something], c.otherthing as [my_otherthing] from tableA a, tableC c where a.the_date > c.the_date

what I'd like to do is do a UNION of the two queries, and group the results based on [my_something]

I've tried to do this, but for some reason query analyzer complains no matter what I try.

How should this query be done? If you don't mind, I'd greatly appreciate sample code of how to group by of the two queries after they've been UNIONed.

Thanks in advance.

Karen

 

[Catadmin]

Karen,

Union statements have to contain the exact same SELECT data as each other. So, as long as your b.OtherThing and c.OtherThing are the same exact datatype & size, you should be able to do it.

(Select A.something as [my_something], b.otherthing as [my_otherthing] 
from tableA a, tableB b where a.the_date > b.the_date)

UNION ALL 

(select a.something as [my_something], c.otherthing as [my_otherthing] from tableA a, tableC c where a.the_date > c.the_date)

I put the SELECT / JOIN statements in parens just to make the code easier for me to read. It is not vital to the success of your query.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Grouping is not the same thing as ordering. You group when you use aggregate functions, like SUM, Count, Avg, etc...

If you want the data sorted, then...

select a.something as [my_something], 
       b.otherthing as [my_otherthing] 
from   tableA a, 
       tableB b 
where  a.the_date > b.the_date

Union

select a.something as [my_something], 
       c.otherthing as [my_otherthing] 
from   tableA a, 
       tableC c 
where  a.the_date > c.the_date

[!]Order By my_something[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[karen4201]

thanks for the tips.

How would I do the UNION, and then GROUP BY my_otherthing ?

What am I doing wrong in the code below?
I've tried this, but it doesn't like it:

select
count(DISTINCT(my_something)) as [the_count], my_otherthing as [my_otherthing]
from

(Select A.something as [my_something], b.otherthing as [my_otherthing]
from tableA a, tableB b where a.the_date > b.the_date)

UNION ALL

(select a.something as [my_something], c.otherthing as [my_otherthing] from tableA a, tableC c where a.the_date > c.the_date)

group by my_otherthing

 

[gmmastros]

Try this...

select count(DISTINCT(my_something)) as [the_count],
       my_otherthing as [my_otherthing]
from 
       (	
       Select  A.something as [my_something], 
               b.otherthing as [my_otherthing]
       from    tableA a, 
               tableB b 
       where   a.the_date > b.the_date

       UNION ALL

       select  a.something as [my_something], 
               c.otherthing as [my_otherthing] 
       from    tableA a, 
               tableC c 
       where   a.the_date > c.the_date
       ) As A
group by my_otherthing

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Karen,

What you're doing wrong is that you're including extra columns in your first group SELECT. As I said above, you have to have the exact same # of items and datatypes in both halves of the UNION statement.

BTW, that's the reason why George put the UNION query in parens. His code should work perfectly for you.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[karen4201]

thanks! I discovered that I had "UNION" but not "UNION ALL". Also, I included the alias of the result from the union all, and now it's works.

Thanks a bunch!

-K

 

[Catadmin]

Glad we could help. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"