troubleshooting Question

Query syntax

Avatar of mpdillon
mpdillon asked on
Microsoft SQL Server
4 Comments1 Solution261 ViewsLast Modified:
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

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros