SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 subquery in WHERE clause and aggregate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  17:41:56  Show Profile  Reply with Quote
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)

Singapore
17645 Posts

Posted - 03/04/2014 :  17:43:30  Show Profile  Reply with Quote
should this column comes from F rather than R ?
AND F.BusinessType = 'Specialty Bike Shop'



KH
Time is always against us

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/04/2014 :  17:57:14  Show Profile  Reply with Quote
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.
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  17:59:26  Show Profile  Reply with Quote
khtan, no I double checked. It's from R.
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  18:09:09  Show Profile  Reply with Quote
Lamprey, how do I post table structure and sample data? I tried windows snipping tool but this forum won't let me paste pictures.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 03/04/2014 :  19:09:21  Show Profile  Reply with Quote
these also from R ?
HAVING R.AnnualSales > AVG(R.AnnualSales)





KH
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  19:12:48  Show Profile  Reply with Quote
yes khtan. annualsales are from R
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 03/04/2014 :  19:48:22  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  21:23:15  Show Profile  Reply with Quote
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 03/04/2014 :  21:52:04  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  22:16:58  Show Profile  Reply with Quote
khtan, thanks but it doesn't return any rows.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 03/04/2014 :  22:36:14  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  23:38:22  Show Profile  Reply with Quote
You are right khtan. the answer is wrong. Thanks for your input.

Edited by - rv498 on 03/04/2014 23:40:07
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/04/2014 :  23:48:55  Show Profile  Reply with Quote
khtan, can you look at my other post about nested subqueries? Thanks.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/05/2014 :  12:05:24  Show Profile  Reply with Quote
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'
		)
Go to Top of Page

rv498
Yak Posting Veteran

USA
60 Posts

Posted - 03/05/2014 :  14:26:27  Show Profile  Reply with Quote
Lamprey, you are the man, I got correct number of rows, thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000