Caluculating the percentage of sales for individual items

Question

Caluculating the percentage of sales for individual items

ITHELP85

Valued Member

Points: 59
More actions
November 28, 2008 at 12:27 pm

#196657

I want to create a query that shows the StoreName, StoreCountry, ItemNumber, ItemName, and that items percentage of the total sales for an item. This is my code below; it is not working.
I want:
The total quantity of a given as a percentage of the total quantity sold in the entire result set
/* Displays Store Name and Items that were Purchased from that Store that have been in Sales Transactions and the Items Sales percentage */
CREATE VIEW STORE_SALES_ITEMS99 ([Store Name], [Store Country], [Item #], [Item Name], [% of Sales])
AS
SELECT DISTINCT st.StoreName, st.StoreCountry, si.ItemNumberSK, si.ItemName,(si.Qty)/(SUM(si.Qty))
FROM STORE st, ITEM_PURCHASE ip, SALES_ITEM si
WHERE st.StoreNumberSK = ip.StoreNumberSK
AND ip.ItemNumberSK = si.ItemNumberSK
GROUP BY st.StoreName, st.StoreCountry, si.ItemNumberSK, si.ItemName
CREATE TABLE STORE(
StoreNumberSK int NOT NULL IDENTITY (1, 1),
StoreName char(40) NOT NULL,
StoreAddress char(30) NOT NULL,
StoreCity char(50) NOT NULL,
StoreCountry char(50) NOT NULL,
StorePhone numeric(10, 0) NOT NULL,
StoreFax numeric(10, 0) NOT NULL,
StoreEmail varchar(50) NOT NULL,
StoreContact char(30) NOT NULL,
CONSTRAINT StorePK PRIMARY KEY (StoreNumberSK),
/* Creates ITEM_PURCHASE table */
CREATE TABLE ITEM_PURCHASE(
ItemNumberSK int NOT NULL IDENTITY (10000, 1),
StoreNumberSK int NOT NULL,
ItemName char(50) NOT NULL,
Date smalldatetime NOT NULL,
LocalCurrencyAmt decimal (12, 6) NOT NULL,
ExchangeRate decimal (12, 6) NOT NULL,
Quantity numeric(7, 0) NOT NULL,
CONSTRAINT Item_PurchasePK PRIMARY KEY (ItemNumberSK),
CONSTRAINT Item_PurchaseFK FOREIGN KEY (StoreNumberSK) REFERENCES STORE (StoreNumberSK)
);
* Creates SALES_ITEM table */
CREATE TABLE SALES_ITEM(
InvoiceNumberSK int NOT NULL,
ItemNumberSK int NOT NULL,
ItemName char(50) NULL,
Qty numeric(7, 0) NOT NULL,
UnitPrice money NULL,
ExtendedPrice AS CASE
WHEN UnitPrice > 0 AND Qty > 0 Then UnitPrice * Qty
END
CONSTRAINT Sales_ItemPK PRIMARY KEY (InvoiceNumberSK, ItemNumberSK),
CONSTRAINT Sales_ItemInvoiceNumberFK FOREIGN KEY (InvoiceNumberSK) REFERENCES SALES (InvoiceNumberSK),
CONSTRAINT Sales_ItemItemNumberSKFK FOREIGN KEY (ItemNumberSK) REFERENCES WAREHOUSE (ItemNumberSK)
);

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Like this:

/* Displays Store Name and Items that were Purchased from that Store that have been in Sales Transactions and the Items Sales percentage */

CREATE VIEW STORE_SALES_ITEMS99

([Store Name], [Store Country], [Item #], [Item Name], [% of Sales])

AS

SELECT st.StoreName

, st.StoreCountry

, si.ItemNumberSK

, si.ItemName

, Count(*)/Cast(MAX(Totals.Num) as Float)

FROM STORE st

Join ITEM_PURCHASE ip ON st.StoreNumberSK = ip.StoreNumberSK

Join SALES_ITEM si ON ip.ItemNumberSK = si.ItemNumberSK

Join (Select ip2.ItemNumberSK, Count(*) as Num

From ITEM_PURCHASE ip2

Group By ip2.ItemNumberSK) as Totals

ON Totals.ItemNumberSK = ip.ItemNumberSK

GROUP BY st.StoreName, st.StoreCountry, si.ItemNumberSK, si.ItemName

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Here's how I'd do it:

;With cteItemTotalSales As

(

Select

IP.ItemNumberSK,

Sum(SI.Qty) as total_sold

From

item_purchase IP Join

sales_item SI On

IP.ItemNumberSK = SI.ItemNumberSK

Group By

IP.ItemNumberSK

)

SELECT

st.StoreName,

st.StoreCountry,

si.ItemNumberSK,

si.ItemName,

(SUM(si.Qty)/Convert(Float, total_sold)) as sales_pct

FROM

STORE st Join

ITEM_PURCHASE ip On

st.StoreNumberSK = ip.StoreNumberSK Join

SALES_ITEM si On

ip.ItemNumberSK = si.ItemNumberSK Join

cteItemTotalSales ITS On

IP.ItemNumberSK = ITS.ItemNumberSK

GROUP BY

st.StoreName,

st.StoreCountry,

si.ItemNumberSK,

si.ItemName

It's not a view, but you didn't specify that you needed a view. If it has to be a view change the CTE to a derived table in the FROM clause.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Okay, Barry beat me to it. He used the derived table and I used the CTE, but the results should be the same unless I made mistake.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jack Corbett (11/28/2008)
Here's how I'd do it:
;With cteItemTotalSales As
(
Select
IP.ItemNumberSK,
Sum(SI.Qty) as total_sold
From
item_purchase IP Join
sales_item SI On
IP.ItemNumberSK = SI.ItemNumberSK
Group By
IP.ItemNumberSK
)
SELECT
st.StoreName,
st.StoreCountry,
si.ItemNumberSK,
si.ItemName,
(SUM(si.Qty)/Convert(Float, total_sold)) as sales_pct
FROM
STORE st Join
ITEM_PURCHASE ip On
st.StoreNumberSK = ip.StoreNumberSK Join
SALES_ITEM si On
ip.ItemNumberSK = si.ItemNumberSK Join
cteItemTotalSales ITS On
IP.ItemNumberSK = ITS.ItemNumberSK
GROUP BY
st.StoreName,
st.StoreCountry,
si.ItemNumberSK,
si.ItemName
It's not a view, but you didn't specify that you needed a view. If it has to be a view change the CTE to a derived table in the FROM clause.

Jack, why would you convert the CTE to a derived table in the FROM clause to make it a view?? I use CTE's frequently when creating views that would have used derived tables prior to SQL Server 2005.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jack: You'll probably need a MAX or MIN on your "total_sold" in the columns list.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Lynn,

Yes you are right, I actually don't use views all that often and wasn't sure if a CTE could be used in a view. Then I got an error when I tried it so I threw in that note.

Barry,

Yeah, you are right too, you either need to include the total_sold column in the group by or add and aggregate function.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Jack Corbett (11/28/2008)
If it has to be a view change the CTE to a derived table in the FROM clause.

Strangely enough, I've not tried using a CTE in a view... are you saying you can't use a CTE in a view?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Jeff Moden (11/28/2008)
Jack Corbett (11/28/2008)
If it has to be a view change the CTE to a derived table in the FROM clause.
Strangely enough, I've not tried using a CTE in a view... are you saying you can't use a CTE in a view?

Never mind... I just saw Lynn's response. Thanks, anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ITHELP85 Valued Member Points: 59 More actions · Answer 9

Jack Corbett (11/28/2008)
Here's how I'd do it:
;With cteItemTotalSales As
(
Select
IP.ItemNumberSK,
Sum(SI.Qty) as total_sold
From
item_purchase IP Join
sales_item SI On
IP.ItemNumberSK = SI.ItemNumberSK
Group By
IP.ItemNumberSK
)
SELECT
st.StoreName,
st.StoreCountry,
si.ItemNumberSK,
si.ItemName,
(SUM(si.Qty)/Convert(Float, total_sold)) as sales_pct
FROM
STORE st Join
ITEM_PURCHASE ip On
st.StoreNumberSK = ip.StoreNumberSK Join
SALES_ITEM si On
ip.ItemNumberSK = si.ItemNumberSK Join
cteItemTotalSales ITS On
IP.ItemNumberSK = ITS.ItemNumberSK
GROUP BY
st.StoreName,
st.StoreCountry,
si.ItemNumberSK,
si.ItemName
It's not a view, but you didn't specify that you needed a view. If it has to be a view change the CTE to a derived table in the FROM clause.

Thanks, but it did not output the percentage, under sales_pct it displays 1 for each row. Looking at your code I am still stomped on how to make it display the percentage.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Did you try mine?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

ITHELP85 Valued Member Points: 59 More actions · Answer 11

rbarryyoung (11/28/2008)
Did you try mine?

Yes, but under Percent of sales I got whole numbers. From looking at your code it seems like you were counting the rows. I tried that before but realized my math would be incorrect. I think the best way for what I am doing is to get the (Total sold for an item) / ( Total Items Sold). Do you know the best way to do this?

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

ITHELP85 (11/28/2008)
rbarryyoung (11/28/2008)
Did you try mine?
Yes, but under Percent of sales I got whole numbers.

Getting "only whole numbers" from an equation is almost always a datatype problem, easily fixed, and not an error in the math.

From looking at your code it seems like you were counting the rows. I tried that before but realized my math would be incorrect. I think the best way for what I am doing is to get the (Total sold for an item) / ( Total Items Sold).

Here is your original expression:(si.Qty)/(SUM(si.Qty)) This was incorrect for the Group By, but I assume from this that you actually wanted:

SUM(Store's Sales of Item * SalesItem.Qty)/SUM(All Sales of Item * SalesItem.Qty)

however, the problem with this approach is that according to your schema, the QTY values are fixed by the Item, not by the Store or even the individual sale (ITEM_PURCHASE). Since the Item is the same on both sides of the division, then the 2 Item.Qty's sre the same also and they cancel each other out.

So you do not need si.Qty, it contributes nothing to the calculation.

So now, you should try my query again, corrected for datatypes:

/* Displays Store Name and Items that were Purchased from that Store that

have been in Sales Transactions and the Items Sales percentage */

CREATE VIEW STORE_SALES_ITEMS99

([Store Name], [Store Country], [Item #], [Item Name], [% of Sales])

AS

SELECT st.StoreName

, st.StoreCountry

, si.ItemNumberSK

, si.ItemName

, Cast(Count(*) as Float)/Cast(MAX(Totals.Num) as Float)

FROM STORE st

Join ITEM_PURCHASE ip ON st.StoreNumberSK = ip.StoreNumberSK

Join SALES_ITEM si ON ip.ItemNumberSK = si.ItemNumberSK

Join (Select ip2.ItemNumberSK, Count(*) as Num

From ITEM_PURCHASE ip2

Group By ip2.ItemNumberSK) as Totals

ON Totals.ItemNumberSK = ip.ItemNumberSK

GROUP BY st.StoreName, st.StoreCountry, si.ItemNumberSK, si.ItemName

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Hmm, it occurs to me now, that there is another possibility here, that instead of si.Qty, you really intended to use ip.Quantity, which can vary for every Item Purchase. In that case, then you would want something like this:

CREATE VIEW STORE_SALES_ITEMS99

([Store Name], [Store Country], [Item #], [Item Name], [% of Sales])

AS

SELECT st.StoreName

, st.StoreCountry

, si.ItemNumberSK

, si.ItemName

, CAST(SUM(ip.Quantity) as Float)/Cast(MAX(Totals.Num) as Float)

FROM STORE st

Join ITEM_PURCHASE ip ON st.StoreNumberSK = ip.StoreNumberSK

Join SALES_ITEM si ON ip.ItemNumberSK = si.ItemNumberSK

Join (Select ip2.ItemNumberSK, SUM(ip2.Quantity) as Num

From ITEM_PURCHASE ip2

Group By ip2.ItemNumberSK) as Totals

ON Totals.ItemNumberSK = ip.ItemNumberSK

GROUP BY st.StoreName, st.StoreCountry, si.ItemNumberSK, si.ItemName

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Let us know how these work out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]