Group by Problems

[ree32]

I know when you are using group by functions you have to include all
the columns in the GROUP BY clause.

But what I am having problems when using a case statement to determine
whether to sum of not a column.
eg.
SELECT Country,
Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
FROM WORLDTABLE
GROUP BY Country
The problem is that this statement will not work as it says that Age
should be in the group by clause. But if include it, then there is no
point in using the SQL statement.

So is there another way to do this than using SELECT statements for
columns as I am worried that it will be inefficient and hog system
resources.

[SQLJunkie]

Don't know if this is the best way (depends on number of records in
table WorldTable and number of users on it) but here is one thing you
can do...
SELECT
WT.Country
, ISNULL(T1.YouthIncome, NULL) AS YouthIncome
, ISNULL(T2.PensionIncome, NULL) AS PensionIncome
FROM
dbo.WorldTable AS WT
LEFT OUTER JOIN (
SELECT
W1.Country
, SUM(W1.Income) AS YouthIncome
FROM
dbo.WorldTable AS W1
WHERE
W1.Age < 15
GROUP BY
W1.Country )
AS T1 ON WT.Country = T1.Country
LEFT OUTER JOIN (
SELECT
W2.Country
, SUM(W2.Income) AS PensionIncome
FROM
dbo.WorldTable AS W2
WHERE
W2.Age > 65
GROUP BY
W2.Country )
AS T2 ON WT.Country = T2.Country
ORDER BY
WT.Country

[Mark]

re***@hotmail.com (ree32) wrote in message news:<76**************************@posting.google. com>...

I know when you are using group by functions you have to include all
the columns in the GROUP BY clause.

But what I am having problems when using a case statement to determine
whether to sum of not a column.
eg.
SELECT Country,
Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
FROM WORLDTABLE
GROUP BY Country
The problem is that this statement will not work as it says that Age
should be in the group by clause. But if include it, then there is no
point in using the SQL statement.

So is there another way to do this than using SELECT statements for
columns as I am worried that it will be inefficient and hog system
resources.

Try this

SELECT Country,sum(YouthIncome) as YouthIncome,sum(PensionIncome) as PensionIncome
FROM (
SELECT Country,
Case WHEN Age<15 THEN Income ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Income ELSE NULL END AS PensionIncome
FROM WORLDTABLE) as Deriv
GROUP BY Country

[David Portas]

SELECT country,
SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
FROM WORLDTABLE
GROUP BY Country

--
David Portas
SQL Server MVP
--

[ree32]

"David Portas" <RE****************************@acm.org> wrote in message news:<11*********************@l41g2000cwc.googlegr oups.com>...

SELECT country,
SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
FROM WORLDTABLE
GROUP BY Country

Thanks this is what I was looking for. Nice and easy.

Other posters thanks too.