Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sales by product category Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 03, 2014 3:44 AM
Points: 5, Visits: 18
Return the number of sales by product category where the average recommended price is
R10 or more greater than the average sales price.
Post #1498125
Posted Wednesday, September 25, 2013 12:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
Is this a homework question?

Also, please provide table DDLs and sample data please.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1498139
Posted Wednesday, September 25, 2013 12:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 03, 2014 3:44 AM
Points: 5, Visits: 18
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
Post #1498142
Posted Wednesday, September 25, 2013 12:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:46 AM
Points: 6, Visits: 178
Create Script
CREATE TABLE [dbo].[Sales](
[Department] [nvarchar](50) NULL,
[price] [int] NULL
)

Sample data taken

Department price
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
)
Post #1498143
Posted Friday, October 04, 2013 12:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
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)

Post #1501428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse