Den 2012-04-20 11:30 skrev Svein Erling Tysv�r s�h�r:

>
> >Hi,
> >
> >I have a case where I extract data with the following SQL:
> >
> >Select Gender_Name, Race_Name, Fop_Name, count(ID) as
> Number_2008_2009 from V_Register_Fop where RegStatus_ID = 1 and
> >Register_RegDate >= '01.04.2008' and Register_RegDate <= '01.04.2009'
> >group by Gender_Name, Race_Name, Fop_Name
> >
> >But then I have to execute the SQL again for the next bookyear up to
> the current one.
> >
> >Is it posible to create one SQL select statement where I can have the
> result as:
> >
> >GENDER_NAME, RACE_NAME, FOP_NAME, NUMBER_2008_2009, NUMBER_2009_2010,
> NUMBER_2010_2011, NUMBER_2011_2012
>
> Sure, just do something like
>
> Select Gender_Name, Race_Name, Fop_Name,
> count(case when RegDate Between '01.04.2008' and '31.03.2009' then ID
> else null end) as Number_2008_2009,
> count(case when RegDate Between '01.04.2009' and '31.03.2010' then ID
> else null end) as Number_2009_2010
> from V_Register_Fop where RegStatus_ID = 1
> group by Gender_Name, Race_Name, Fop_Name
>
> Actually, I haven't tested this one, it might be that you must add
> DISTINCT or change to
>
> sum(case when RegDate Between '01.04.2008' and '31.03.2009' then 1
> else 0 end) as Number_2008_2009,
>
> (I never use COUNT(FIELDNAME), always either COUNT(*) or
> COUNT(DISTINCT FIELDNAME)).
>
>

I usually do it like this:

Select Gender_Name, Race_Name, Fop_Name,
count(case when RegDate Between '01.04.2008' and '31.03.2009' then 1
end) as Number_2008_2009,
count(case when RegDate Between '01.04.2009' and '31.03.2010' then 1
end) as Number_2009_2010
from V_Register_Fop where RegStatus_ID = 1
group by Gender_Name, Race_Name, Fop_Name

No need to add "else null" in a case statement, because that's what
happens by default. Also, no need to put anything special in the then
expression - just use a constant 1. The count will count the number of
ones, ignoring the nulls.

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



[Non-text portions of this message have been removed]