SQL Select

23 Jun 2006 1:54 PM

magix

have following table record

Year          ModelYr       Type
1999           1994            2
1999           1999            3
2000           1999            4
2001           2000            2
1999           1996            4
2000           1996            5
2000           1998            3
2001           2001            2

Here are the rules:
1. In display output, it will have the following column name:
    Year        TypeA     TypeB

2. "ModelYr" is equal or less than "Year"

3. if ("Year" - "ModelYr") < 2 then
        TypeA = "Type"
    else
        TypeB = "Type"

3. The output should be "Group by Year Order by Year"
4. For each "Year", the each type will be SUM UP.

So the expected output will be:
Year               TypeA              TypeB
1999                 6                       3
2000                 7                       5
2001                 4                       0

Could you kindly advise how to make the SELECT statement to achieve the
output above ? I'm really not too sure.

Any help is very much appreciated.

Thank you.

Regards.

23 Jun 2006 2:13 PM

Tracy McKibben

magix wrote:
Show quote

> have following table record
>
> Year          ModelYr       Type
> 1999           1994            2
> 1999           1999            3
> 2000           1999            4
> 2001           2000            2
> 1999           1996            4
> 2000           1996            5
> 2000           1998            3
> 2001           2001            2
>
> Here are the rules:
> 1. In display output, it will have the following column name:
>     Year        TypeA     TypeB
>
> 2. "ModelYr" is equal or less than "Year"
>
> 3. if ("Year" - "ModelYr") < 2 then
>         TypeA = "Type"
>     else
>         TypeB = "Type"
>
> 3. The output should be "Group by Year Order by Year"
> 4. For each "Year", the each type will be SUM UP.
>
> So the expected output will be:
> Year               TypeA              TypeB
> 1999                 6                       3
> 2000                 7                       5
> 2001                 4                       0
>
>
> Could you kindly advise how to make the SELECT statement to achieve the
> output above ? I'm really not too sure.
>
> Any help is very much appreciated.
>
> Thank you.
>
> Regards.
>
>
>

SELECT
    Year,
    SUM(CASE WHEN Year - ModelYr < 2 THEN Type ELSE 0 END) AS TypeA,
    SUM(CASE WHEN Year - ModelYr >= 2 THEN Type ELSE 0 END) AS TypeB
FROM Table
GROUP BY Year

23 Jun 2006 2:46 PM

magix

sorry, the expected output should be (3,6 instead of 6,3):

Year               TypeA              TypeB
1999                 3                       6
2000                 7                       5
2001                 4                       0

Show quote

"magix" <ma***@asia.com> wrote in message
news:449bf28b$1_1@news.tm.net.my...
> have following table record
>
> Year          ModelYr       Type
> 1999           1994            2
> 1999           1999            3
> 2000           1999            4
> 2001           2000            2
> 1999           1996            4
> 2000           1996            5
> 2000           1998            3
> 2001           2001            2
>
> Here are the rules:
> 1. In display output, it will have the following column name:
>    Year        TypeA     TypeB
>
> 2. "ModelYr" is equal or less than "Year"
>
> 3. if ("Year" - "ModelYr") < 2 then
>        TypeA = "Type"
>    else
>        TypeB = "Type"
>
> 3. The output should be "Group by Year Order by Year"
> 4. For each "Year", the each type will be SUM UP.
>
> So the expected output will be:
> Year               TypeA              TypeB
> 1999                 6                       3
> 2000                 7                       5
> 2001                 4                       0
>
>
> Could you kindly advise how to make the SELECT statement to achieve the
> output above ? I'm really not too sure.
>
> Any help is very much appreciated.
>
> Thank you.
>
> Regards.
>
>
>