subquery in WHERE clause and aggregate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subquery in WHERE clause and aggregate

Author

Topic

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 17:41:56

So I wrote this code but keep getting an error. Your input please.

List all resellers whose annual sales exceed the average annual sales for resellers whose Business Type is "Specialty Bike Shop". Show Business type, Reseller Name, and annual sales. Use appropriate subqueries.
-- 396 Rows

SELECT R.ResellerName
FROM dbo.DimReseller AS R
WHERE
R.ResellerKey IN (
SELECT F.ResellerKey
FROM dbo.FactResellerSales AS F
WHERE R.ResellerKey = F.ResellerKey AND R.BusinessType = 'Specialty Bike Shop'
GROUP BY F.ResellerKey
HAVING R.AnnualSales > AVG(R.AnnualSales)
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 17:43:30

should this column comes from F rather than R ?

AND F.BusinessType = 'Specialty Bike Shop'

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-04 : 17:57:14

It looks like there are several things wrong here. So, it would be better if you can post your table structure and some sample data. I'd start by starting with smaller queries.
Can you get the average annual sales for resellers whose Business Type is "Specialty Bike Shop". If you get that query working, then you can start to build on it.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 17:59:26

khtan, no I double checked. It's from R.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 18:09:09

Lamprey, how do I post table structure and sample data? I tried windows snipping tool but this forum won't let me paste pictures.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 19:09:21

these also from R ?

HAVING R.AnnualSales > AVG(R.AnnualSales)

KH
[spoiler]Time is always against us[/spoiler]

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 19:12:48

yes khtan. annualsales are from R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 19:48:22

you sure it is from table DimReseller ? by the table name, it looks like AnnualSales is a column of FactResellerSales.

Can you post both the table's schema ?

KH
[spoiler]Time is always against us[/spoiler]

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 21:23:15

so using server studio 2012 script I copied the following:

REATE TABLE [dbo].[DimReseller](
[ResellerKey] [int] IDENTITY(1,1) NOT NULL,
[GeographyKey] [int] NULL,
[ResellerAlternateKey] [nvarchar](15) NULL,
[Phone] [nvarchar](25) NULL,
[BusinessType] [varchar](20) NOT NULL,
[ResellerName] [nvarchar](50) NOT NULL,
[NumberEmployees] [int] NULL,
[OrderFrequency] [char](1) NULL,
[OrderMonth] [tinyint] NULL,
[FirstOrderYear] [int] NULL,
[LastOrderYear] [int] NULL,
[ProductLine] [nvarchar](50) NULL,
[AddressLine1] [nvarchar](60) NULL,
[AddressLine2] [nvarchar](60) NULL,
[AnnualSales] [money] NULL,
[BankName] [nvarchar](50) NULL,
[MinPaymentType] [tinyint] NULL,
[MinPaymentAmount] [money] NULL,
[AnnualRevenue] [money] NULL,
[YearOpened] [int] NULL,
CONSTRAINT [PK_DimReseller_ResellerKey] PRIMARY KEY CLUSTERE

CREATE TABLE [dbo].[FactResellerSales](
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[DueDateKey] [int] NOT NULL,
[ShipDateKey] [int] NOT NULL,
[ResellerKey] [int] NOT NULL,
[EmployeeKey] [int] NOT NULL,
[PromotionKey] [int] NOT NULL,
[CurrencyKey] [int] NOT NULL,
[SalesTerritoryKey] [int] NOT NULL,
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[SalesOrderLineNumber] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[OrderQuantity] [smallint] NULL,
[UnitPrice] [money] NULL,
[ExtendedAmount] [money] NULL,
[UnitPriceDiscountPct] [float] NULL,
[DiscountAmount] [float] NULL,
[ProductStandardCost] [money] NULL,
[TotalProductCost] [money] NULL,
[SalesAmount] [money] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[CustomerPONumber] [nvarchar](25) NULL,
[OrderDate] [datetime] NULL,
[DueDate] [datetime] NULL,
[ShipDate] [datetime] NULL,
CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 21:52:04

looks like you have all the information you need from DimReseller. Use SubQuery to get the Avg AnnualSales


SELECT R.ResellerName
FROM   dbo.DimReseller AS R
WHERE  R.BusinessType = 'Specialty Bike Shop'
AND    R.AnnualSales > (SELECT AVG(AnnualSales) FROM dbo.DimReseller)

KH
[spoiler]Time is always against us[/spoiler]

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 22:16:58

khtan, thanks but it doesn't return any rows.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-04 : 22:36:14

it should have, unless the AnnualSales for Specialty Bike Shop are the same.

run the sub-query itself and see what is the average value for AnnualSales and check that with the data in table

KH
[spoiler]Time is always against us[/spoiler]

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 23:38:22

You are right khtan. the answer is wrong. Thanks for your input.

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-04 : 23:48:55

khtan, can you look at my other post about nested subqueries? Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-05 : 12:05:24

As I mentioned, it is good to break the problem apart until set-based "programming" becomes more intuitive.

You said you wanted to "List all resellers whose annual sales exceed the average annual sales for resellers whose Business Type is "Specialty Bike Shop". If you start with getting the average annual sales for resellers whose Business Type is "Specialty Bike Shop" then, things should be easier. All the code posted so far seems to ignore that.

1. Get the average annual sales for resellers whose Business Type is "Specialty Bike Shop"

SELECT 
	AVG(AnnualSales) AS AverageAnnualSales
FROM 
	dbo.DimReseller
WHERE
	BusinessType = 'Specialty Bike Shop'

2. Combine that sub-query with the base table to get your final result:

SELECT
	ResellerName
	,BusinessType
	,AnnualSales
FROM
	dbo.DimReseller
WHERE
	AnnualSales > 
		(
			SELECT 
				AVG(AnnualSales) AS AverageAnnualSales
			FROM 
				dbo.DimReseller
			WHERE
				BusinessType = 'Specialty Bike Shop'
		)

rv498
Yak Posting Veteran

60 Posts

Posted - 2014-03-05 : 14:26:27

Lamprey, you are the man, I got correct number of rows, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources