sql - Perform a query with aggregation and grouping by joined tables -

currently i'm facing sql challenge , wondering, if approach correct @ all. let's consider following simplified data model:

table cat: ---------- id color  table domestic_cat: ------------------- cat_id domestic_attribute  table persian_cat: ------------------ cat_id persian_attribute 

and let's suppose have following data in tables:

table cat: id   color -------------- 1    'black' 2    'white' 3    'black' 4    'white' 5    'black' 6    'red' 7    'white' 8    'white' 9    'red' 10   'black'  table domestic_cat: cat_id    domestic_attribute ---------------------------- 1         'domestic1' 2         'domestic2' 3         'domestic3' 7         'domestic4' 8         'domestic5'  table persian_cat cat_id    persian_attribute --------------------------- 4         'persian1' 5         'persian2' 6         'persian3' 9         'persian4' 10        'persian5' 

i perform aggregation query following result:

cat_type       cat_color   count --------------------------------- 'domestic_cat'    'black'       2 'domestic_cat'    'white'       3 'persian_cat'     'white'       1 'persian_cat'     'black'       2 'persian_cat'     'red'         2 

as can see, want group 'count'-result following values: - fact, whether given cat domestic or persian - cat's color

the first 1 hard thing - don't know if it's possible @ perform "group joined table"? i'm breaking head cannot find solution :( actual rdbms used oracle 11.

you can use outer joins cat both other tables, , determine table got match, , use populate cat_type column:

select case when dc.cat_id not null 'domestic_cat'     when pc.cat_id not null 'persian_cat' end cat_type,   c.color,   count(*) "count" cat c left join domestic_cat dc on dc.cat_id = c.id left join persian_cat pc on pc.cat_id = c.id group case when dc.cat_id not null 'domestic_cat'     when pc.cat_id not null 'persian_cat' end,   c.color order 1,2; 

sql fiddle.

depending on real-world problem might perform better inner join/union option, you'd need try both see better (faster, more efficient, more maintainable...).