Aggregation of 2 colums in result

Question

i am trying to aggregate two columns in my result table but I can't get my head around it... I have a star schema and the following query:

select 1und1.SubCategory,count(industry_facts.NACE_ID), statistics_2016.businesses 
from industry_facts, 1und1, statistics_2016
where 1und1.NACE_ID = industry_facts.NACE_ID
and statistics_2016.Stat_ID = industry_facts.Stat_ID
group by 1und1.SubCategory ASC, statistics_2016.businesses

The result table give back the correct results but in this style:

This is the Schema:

I can't figure out a way to aggregate it per SubCategory...So that it looks like Accomodation, 660, 1004390

Tried it with rollup at the and of the group by clause, but that only aggregates the 2nd column, the 3rd always gives back "NULL"

Do you have any ideas? Help is much appreciated!

Answer 1

Remove business from the GROUP BY. Also, never use commas in the FROM clause. Always use proper, explicit JOIN syntax:

select u.SubCategory, count(i.NACE_ID)
from industry_facts join i
     1und1 u
     on u.NACE_ID = i.NACE_ID join
     statistics_2016 s 
     on s.Stat_ID = i.Stat_ID
group by u.SubCategory ASC;

If you want the businesses, then you can use group_concat(s.businesses) in the SELECT or group_concat(distinct s.businesses).

Answer 2

Try with this code:

   select 1und1.SubCategory,count(industry_facts.NACE_ID), statistics_2016.businesses 
    from industry_facts inner join 1und1
    on industry_facts.NACE_ID=1und1.NACE_ID
    join statistics_2016
    on statistics_2016.Stat_ID = industry_facts.Stat_ID
    group by 1und1.SubCategory,statistics_2016.businesses
    order by 1und1.SubCategory