Grouping Problem

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')