Link to home
Start Free TrialLog in
Avatar of mpdillon
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.

/****** 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

Open in new window


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
Avatar of Dulton
Dulton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mpdillon
mpdillon

ASKER

Thanks guys,
It is the Count (Distinct Yr) that I couldn't piece together. I am going to use dsacker code. Thanks again everyone.
pat