count(*)...group by from two separate tables 1

[bardley]

Let's say I have table A:

ID | ATTR1 | ATTR2 | ...

and table B:

ID | ATTR1 | ATTR3 | ...

and I wanted to return all distinct values of ATTR1 with their combined counts from both tables. Maybe I'm out to lunch, but I can't figure out a good way to do this. It must be a join along with some kind of group by, so if you are having a better SQL day than I am, please help!!
Thanx! Brad Gunsalus
Cymtec Systems, Inc.
bgunsalus@cymtec.com

 

[tlbroadbent]

If I understand correctly, I believe you'll need a union query. It will become a subquery of an outer aggregate query.

Select q.Attr1, count(*) As RecCnt From
(Select Attr1, ID From TableA
UNION ALL
Select Attr1, ID From TableB) As q
Group By q.Attr1

Let me know if I've misunderstood the requirement. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bardley]

Belated thanks for the quick help! Now the plot thickens:

I need a query that looks like this:

select ??? from (
 select substr(timestamp,0,10) 
 from table1 
 where substr(timestamp,0,10) < '2001-11-02'
 and substr(timestamp,0,10) > '2001-10-26'
 union all
 select substr(timestamp,0,10) 
 from table2 
 where substr(timestamp,0,10) < '2001-11-02'
 and substr(timestamp,0,10) > '2001-10-26'
) 
group by ???
order by 1

Oracle 8.i DB, and it doesn't like "AS q" after the sub-query. I need to know how to reference the field that sub-query returns, because it's no longer "timestamp". As if that weren't enough, I also need to group by that field!

The inner query works fine.

Thanks to whomever for any help they can offer.
Brad Gunsalus
Cymtec Systems, Inc.
bgunsalus@cymtec.com

 

[tlbroadbent]

I'm unfamilar with Oracle but assume it allows the use of aliases as that is very basic to SQL. In SQL Server the keyword "As" is optional.

select NewTime from (
select substr(timestamp,0,10) As NewTime
.
.
.

SQL Server also allows me to create teh alias name this way.

select NewTime from (
select NewTime = substr(timestamp,0,10)
.
.
.

Sorry that I don't know the exact Oracle syntax. I would recommend using an Oracle forum from this point forward to get answers to Oracle specific questions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[carp]

It won't take AS q AFTER the subquery - however, it WILL take AS q WITHIN the subquery:

select q from ( select substr(timestamp,0,10) AS q
from table1
where substr(timestamp,0,10) < '2001-11-02'
and substr(timestamp,0,10) > '2001-10-26'
union all
select substr(timestamp,0,10)
from table2
where substr(timestamp,0,10) < '2001-11-02'
and substr(timestamp,0,10) > '2001-10-26'
) x
group by q
order by 1;

Also, there is no need for the &quot;ORDER BY&quot; - Oracle will sort the columns in the order they appear in your GROUP BY clause. Consequently, ORDER BY is incurring an additional sort for no reason.