September 24, 2013 at 11:16 pm
Return the number of sales by product category where the average recommended price is
R10 or more greater than the average sales price.
September 25, 2013 at 12:42 am
Is this a homework question?
Also, please provide table DDLs and sample data please.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2013 at 12:49 am
yes it is i only have
three table
tblCustomer
customerID
Firstname
lastname
city
state
postCode
tblSales
SalesID
productID
customerID
salePrice
saleDate
tblProducts
productID
parentProductID
productName
recommendedPRice
catercory
September 25, 2013 at 12:51 am
Create Script
CREATE TABLE [dbo].[Sales](
[Department] [nvarchar](50) NULL,
[price] [int] NULL
)
Sample data taken
Departmentprice
A 10
B 5
A 15
A 3
B 15
C 11
D 3
D 2
SELECT DEPARTMENT,PRICE FROM [DBO].[SALES] A
WHERE DEPARTMENT
IN(
SELECT DEPARTMENT FROM [DBO].[SALES] B WHERE
A.DEPARTMENT =B.DEPARTMENT
GROUP BY DEPARTMENT Having AVG(B.PRICE) >=10
)
October 4, 2013 at 12:15 am
Following query will get you result for
Return the number of sales by product category where the average recommended price is
R10 or more
SELECT COUNT(S.SalesID)
FROM tblSales AS s
INNER JOIN tblProducts AS P ON
S.productID = P.productID
GROUP BY p.catercory
HAVING AVG(P.recommendedPRice) >= 10
If you want to also check for recommended price greater than the average sales price you have to add following addtional checks
OR AVG(P.recommendedPRice) > AVG(S.salePrice)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply