Databases Reference
In-Depth Information
Schauen Sie sich nun den gesamten Select an. Die Ermittlung der Gesamtzahl pro Alters-
gruppe haben wir hinten angefügt.
Select aw.Wert,aw.Altersgruppe,aw.Anzahl, ag2.Gesamt,
Cast((cast (aw.Anzahl as numeric)/ag2.Gesamt*100)as decimal (5,2))'%-Anteil'
from
(Select Wert,
CASE WHEN DATEDIFF(YEAR,birthdate,GETDATE())< 20 then '< 20'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 20 and 29 then
'20 - 29'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 30 and 39 then
'30 - 39'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 40 and 49 then
'40 - 49'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 50 and 59 then
'50 - 59'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 60 and 69 then
'60 - 69'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())>= 70 then '70 +'
END AS Altersgruppe,COUNT (*) Anzahl
FROM [dbo].[iw_customer]c,[dbo].[customer_value]v
where c.riskID = v.riskID
group by Wert,
CASE WHEN DATEDIFF(YEAR,birthdate,GETDATE())< 20 then '< 20'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 20 and 29 then
'20 - 29'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 30 and 39 then
'30 - 39'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 40 and 49 then
'40 - 49'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 50 and 59 then
'50 - 59'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 60 and 69 then
'60 - 69'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())>= 70 then '70 +'
END )aw, →
order by weglassen
(Select ag.Altersgruppe, count (*)Gesamt
→
Hier wurde der Select angefügt
from
(Select
CASE WHEN DATEDIFF(YEAR,birthdate,GETDATE())< 20 then '< 20'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 20 and 29 then
'20 - 29'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 30 and 39 then
'30 - 39'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 40 and 49 then
'40 - 49'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 50 and 59 then
'50 - 59'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())between 60 and 69 then
'60 - 69'
WHEN DATEDIFF(YEAR,birthdate,GETDATE())>= 70 then '70 +'
END AS Altersgruppe
FROM [dbo].[iw_customer])ag
group by ag.Altersgruppe)ag2 →
order by weglassen
where aw.Altersgruppe = ag2.Altersgruppe →
join
order by aw.Wert,aw.Altersgruppe
