mpdillon
asked on
Query syntax
I would like to construct a query that only returns the SUM if the NAME has all three years. Below is the code to create a table and to populate it with sample data. NOTE: the code does not contain a check to determine if the table already exists.
I would like to write a query which returns the sum of the Amt column but only if the Name exists in all three Years (2009, 2010, 2011).
My failed attempt looks like:
Select Name, Sum(Amt) From Hexis
where Yr in (2009,2010,2011)
Group by Name
Thank you in advance for you assistance.
pat
/****** Object: Table [dbo].[Hexis] Script Date: 07/08/2013 14:28:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hexis](
[IDNo] [bigint] IDENTITY(1,1) NOT NULL,
[Yr] [int] NULL,
[Name] [varchar](50) NULL,
[Amt] [decimal](18, 5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into dbo.Hexis (Yr,Name,Amt)
select 2009,'Wilmington',5
Union All
select 2010,'Wilmington',5
Union All
select 2011,'Wilmington',5
Union All
select 2009,'Philadelphia',5
Union All
select 2010,'Philadelphia',5
Union All
select 2011,'Baltimore',5
Union All
select 2009,'Wilmington',7
Union All
select 2010,'Wilmington',7
Union All
select 2011,'Wilmington',7
Union All
select 2009,'Philadelphia',7
Union All
select 2010,'Philadelphia',7
Union All
select 2011,'Baltimore',7
I would like to write a query which returns the sum of the Amt column but only if the Name exists in all three Years (2009, 2010, 2011).
My failed attempt looks like:
Select Name, Sum(Amt) From Hexis
where Yr in (2009,2010,2011)
Group by Name
Thank you in advance for you assistance.
pat
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is the Count (Distinct Yr) that I couldn't piece together. I am going to use dsacker code. Thanks again everyone.
pat