I hope someone can help me. I am trying to get the columns stadt, größtes, mittleres and kleines to display for the cities München and Stuttgart but I am getting all the columns displayed horizontally . München and Stuttgart should be on 2 different rows. Inner join, left join don't seem to work hence I just listed the display result as select * from cte, cte1. How can I get the proper display I want?
with cte as
(
select left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt ='Stuttgart'
group by stadt
),
cte1 as
(
select left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt ='München'
group by stadt
)
select * from cte, cte1
CodePudding user response:
This is a simple query that would meet your requirements.
select left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt in ('Stuttgart','München')
group by stadt
if you have to have CTE, you an try something like this
with cte as
(select left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
group by stadt
)
select * from cte where stadt in ('Stuttgart','München')
Although I would recommend the former query, there is no reason to have a CTE.