Caluculating the percentage of sales for individual items

[ITHELP85]

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)
);
​

 

[jal]

I take it you are executing this query against Sql Server? Because I wasn't aware of creating views in Access.

First, I don't think you want both DISTINCT and GROUP BY in the same query. They both do the same thing (eliminate dups), except that GROUP BY (behind the scenes) keeps track of each dup in each set of dups so that you can aggregate them (e.g. SUM the dups in a given dup-set).

Since you are looking for an aggregate, use GROUP BY, not DISTINCT.

Normally I use INNER JOIN rather than a WHERE clause, personally I've never tried GROUP BY without INNER JOIN (I doubt that's the problem, though).

As for the logic of your query, I haven't tried to figure it out yet.

 

[jal]

I want to create a query that shows the StoreName, StoreCountry, ItemNumber, ItemName, and that items percentage of the total sales for an item.​

Where total sales for an item is defined as what? For a particular store? The total for all stores put together in one lump sum? If so, you might want to begin with a separate query that aggreates the total for each item and then INNER JOIN that result (the resulting virtual table) to you query above.

 

[ITHELP85]

Where total sales for an item is defined as what? For a particular store? The total for all stores put together in one lump sum? If so, you might want to begin with a separate query that aggreates the total for each item and then INNER JOIN that result (the resulting virtual table) to you query above.

I am trying to display for each item sold the percent of sales it takes up, for all sales

 

[Bilbo_Baggins_Esq]

I am trying to display for each item sold the percent of sales it takes up, for all sales

for all sales per store, or per all stores.

This is an important question to answer, as was asked above.
If you're going to break this down by store, you must first get store totals by each item sold in THAT store, and then the total sales for THAT store. Then the percentage each item has of THAT store's total is easy to calculate.

 

[Rich]

I am trying to display for each item sold the percent of sales it takes up, for all sales

All sales for each store or all sales for all stores?

 

[ITHELP85]

All sales for each store or all sales for all stores?

All sales for all stores