Pulling data out and understanding total queries

J

John T Ingato

I will do my best to explain what I am after. See below SQL and brief table
descriptions at the bottom of this post.

I have a table with inventory counts for each of our 10 products in 1800
stores for every week of the year. Each week I receive the new numbers
which should be 18,000 records. At the end of the year, 52 weeks, I end up
with an inventory table with 936,000 record... in theory that is. The
problem is that not every store reports on every item every week. Some of
the data is missing. I am also having a little trouble understanding exactly
what this recordset represents

Below I am running a totals query to show the performance of each item
nationally, in all 1800 stores. If you look you will see I am grouping each
item and showing the following aggregates for each item:

Total Units Sold per item: SUM(adjSales)
Average sales per week: I am using AVG(adjSales) assuming I am dividing the
total units sold by the total # of records summed together. I also assume
that is the same as total Units sold /(Total of Reporting Stores * Total #
of weeks).

My question is on the two lines
([YTD Units]/([Avg Inventory]*1800)) AS Turns &
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

In the first line I am trying to calculate the turn value which is number of
times the average inventory is sold through. The turns for one store would
be YTDSales / AverageInventory, but since my YTDSales represents all the
stores, I need to figure out the number of stores that have reported amounts
for this item.
I tried to aquire that value in the second line "Count(SELECT DISTINCT
StoreNumber)" but that doesnt work. I get an error that says this query only
returns 1 record at the most.

BOTTOM LINE: Is there a way to fin how many distinct storenumbers were in
the total calculation for each item?
SELECT
tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory],
Format([YTD Units]*[Current Retail],"Currency") AS [Retail $],
Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $],
tblProgramHomeDepot.[Current Cost],
tblProgramHomeDepot.[Current Retail],
([YTD Units]/([Avg Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported],
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot

INNER JOIN tblProductLine ON tblCalculatedSalesDataHomeDepot.ItemNumber =
tblProductLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber =
tblCalculatedSalesDataHomeDepot.StoreNumber) AND
(tblHomeDepotPOSData.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber))

INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber

GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];

*******Tables*********************************************************************
tblCalculatedSalesHomeDepot: FromDate, ToDate,
StoreNumber,SKUNumber,ItemNumber,SKUDescription... (this is a temp.
Calculated table for speed)
tblHomeDepotPOSData: ReportDate,StoreNumber,SKUNumber, ONHand
tblProductLine: ItemNumber, Description,etc
tblProgramHomeDepot: SKUNumber, ItemNumber, Cost, Retail ... linking table
 
J

John Nurick

I will do my best to explain what I am after. See below SQL and brief table
descriptions at the bottom of this post.

I have a table with inventory counts for each of our 10 products in 1800
stores for every week of the year. Each week I receive the new numbers
which should be 18,000 records. At the end of the year, 52 weeks, I end up
with an inventory table with 936,000 record... in theory that is. The
problem is that not every store reports on every item every week. Some of
the data is missing. I am also having a little trouble understanding exactly
what this recordset represents

Below I am running a totals query to show the performance of each item
nationally, in all 1800 stores. If you look you will see I am grouping each
item and showing the following aggregates for each item:

Total Units Sold per item: SUM(adjSales)
Average sales per week: I am using AVG(adjSales) assuming I am dividing the
total units sold by the total # of records summed together. I also assume
that is the same as total Units sold /(Total of Reporting Stores * Total #
of weeks).

My question is on the two lines
([YTD Units]/([Avg Inventory]*1800)) AS Turns &
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

In the first line I am trying to calculate the turn value which is number of
times the average inventory is sold through. The turns for one store would
be YTDSales / AverageInventory, but since my YTDSales represents all the
stores, I need to figure out the number of stores that have reported amounts
for this item.
I tried to aquire that value in the second line "Count(SELECT DISTINCT
StoreNumber)" but that doesnt work. I get an error that says this query only
returns 1 record at the most
Click to expand...

Try something like this (though someone else may suggest a neater way):

(
SELECT COUNT(1) FROM (
SELECT DISTINCT A.StoreNumber
FROM tblCalculatedSalesDataHomeDepot AS A
WHERE A.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber
)
)




BOTTOM LINE: Is there a way to fin how many distinct storenumbers were in
the total calculation for each item?
SELECT
tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory],
Format([YTD Units]*[Current Retail],"Currency") AS [Retail $],
Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $],
tblProgramHomeDepot.[Current Cost],
tblProgramHomeDepot.[Current Retail],
([YTD Units]/([Avg Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported],
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot

INNER JOIN tblProductLine ON tblCalculatedSalesDataHomeDepot.ItemNumber =
tblProductLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber =
tblCalculatedSalesDataHomeDepot.StoreNumber) AND
(tblHomeDepotPOSData.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber))

INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber

GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];

*******Tables*********************************************************************
tblCalculatedSalesHomeDepot: FromDate, ToDate,
StoreNumber,SKUNumber,ItemNumber,SKUDescription... (this is a temp.
Calculated table for speed)
tblHomeDepotPOSData: ReportDate,StoreNumber,SKUNumber, ONHand
tblProductLine: ItemNumber, Description,etc
tblProgramHomeDepot: SKUNumber, ItemNumber, Cost, Retail ... linking table
Click to expand...
 
J

John T Ingato

I'm not even sure I understand the Select Statement you gave.
SELECT COUNT(1) FROM (
SELECT DISTINCT A.StoreNumber
FROM tblCalculatedSalesDataHomeDepot AS A
WHERE A.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber
)
Click to expand...
what does "Count(1)" mean? What does the count function count?

John Nurick said:
I will do my best to explain what I am after. See below SQL and brief
table
descriptions at the bottom of this post.

I have a table with inventory counts for each of our 10 products in 1800
stores for every week of the year. Each week I receive the new numbers
which should be 18,000 records. At the end of the year, 52 weeks, I end up
with an inventory table with 936,000 record... in theory that is. The
problem is that not every store reports on every item every week. Some of
the data is missing. I am also having a little trouble understanding
exactly
what this recordset represents

Below I am running a totals query to show the performance of each item
nationally, in all 1800 stores. If you look you will see I am grouping
each
item and showing the following aggregates for each item:

Total Units Sold per item: SUM(adjSales)
Average sales per week: I am using AVG(adjSales) assuming I am dividing
the
total units sold by the total # of records summed together. I also assume
that is the same as total Units sold /(Total of Reporting Stores * Total #
of weeks).

My question is on the two lines
([YTD Units]/([Avg Inventory]*1800)) AS Turns &
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

In the first line I am trying to calculate the turn value which is number
of
times the average inventory is sold through. The turns for one store would
be YTDSales / AverageInventory, but since my YTDSales represents all the
stores, I need to figure out the number of stores that have reported
amounts
for this item.
I tried to aquire that value in the second line "Count(SELECT DISTINCT
StoreNumber)" but that doesnt work. I get an error that says this query
only
returns 1 record at the most
Click to expand...

Try something like this (though someone else may suggest a neater way):

(
SELECT COUNT(1) FROM (
SELECT DISTINCT A.StoreNumber
FROM tblCalculatedSalesDataHomeDepot AS A
WHERE A.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber
)
)




BOTTOM LINE: Is there a way to fin how many distinct storenumbers were in
the total calculation for each item?
SELECT
tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory],
Format([YTD Units]*[Current Retail],"Currency") AS [Retail $],
Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $],
tblProgramHomeDepot.[Current Cost],
tblProgramHomeDepot.[Current Retail],
([YTD Units]/([Avg Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported],
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot

INNER JOIN tblProductLine ON tblCalculatedSalesDataHomeDepot.ItemNumber =
tblProductLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber =
tblCalculatedSalesDataHomeDepot.StoreNumber) AND
(tblHomeDepotPOSData.SKUNumber =
tblCalculatedSalesDataHomeDepot.SKUNumber))

INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber

GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];

*******Tables*********************************************************************
tblCalculatedSalesHomeDepot: FromDate, ToDate,
StoreNumber,SKUNumber,ItemNumber,SKUDescription... (this is a temp.
Calculated table for speed)
tblHomeDepotPOSData: ReportDate,StoreNumber,SKUNumber, ONHand
tblProductLine: ItemNumber, Description,etc
tblProgramHomeDepot: SKUNumber, ItemNumber, Cost, Retail ... linking table
Click to expand...
Click to expand...
 
J

John Nurick

COUNT() counts the number of records, so (as far as I know) it gives the
same result for any valid expression or fieldname. And it's easier to
type "1" than "A.StoreNumber"!

I'm not even sure I understand the Select Statement you gave.
SELECT COUNT(1) FROM (
SELECT DISTINCT A.StoreNumber
FROM tblCalculatedSalesDataHomeDepot AS A
WHERE A.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber
)
Click to expand...
what does "Count(1)" mean? What does the count function count?

John Nurick said:
I will do my best to explain what I am after. See below SQL and brief
table
descriptions at the bottom of this post.

I have a table with inventory counts for each of our 10 products in 1800
stores for every week of the year. Each week I receive the new numbers
which should be 18,000 records. At the end of the year, 52 weeks, I end up
with an inventory table with 936,000 record... in theory that is. The
problem is that not every store reports on every item every week. Some of
the data is missing. I am also having a little trouble understanding
exactly
what this recordset represents

Below I am running a totals query to show the performance of each item
nationally, in all 1800 stores. If you look you will see I am grouping
each
item and showing the following aggregates for each item:

Total Units Sold per item: SUM(adjSales)
Average sales per week: I am using AVG(adjSales) assuming I am dividing
the
total units sold by the total # of records summed together. I also assume
that is the same as total Units sold /(Total of Reporting Stores * Total #
of weeks).

My question is on the two lines
([YTD Units]/([Avg Inventory]*1800)) AS Turns &
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

In the first line I am trying to calculate the turn value which is number
of
times the average inventory is sold through. The turns for one store would
be YTDSales / AverageInventory, but since my YTDSales represents all the
stores, I need to figure out the number of stores that have reported
amounts
for this item.
I tried to aquire that value in the second line "Count(SELECT DISTINCT
StoreNumber)" but that doesnt work. I get an error that says this query
only
returns 1 record at the most
Click to expand...

Try something like this (though someone else may suggest a neater way):

(
SELECT COUNT(1) FROM (
SELECT DISTINCT A.StoreNumber
FROM tblCalculatedSalesDataHomeDepot AS A
WHERE A.SKUNumber = tblCalculatedSalesDataHomeDepot.SKUNumber
)
)




BOTTOM LINE: Is there a way to fin how many distinct storenumbers were in
the total calculation for each item?
SELECT
tblCalculatedSalesDataHomeDepot.SKUNumber AS SKU,
tblCalculatedSalesDataHomeDepot.ItemNumber,
tblProductLine.SkuDescription,
Sum(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS [YTD Units],
Avg(tblCalculatedSalesDataHomeDepot.[Adj Sales]) AS ASW,
Avg(tblHomeDepotPOSData.OnHand) AS [Avg Inventory],
Format([YTD Units]*[Current Retail],"Currency") AS [Retail $],
Format([YTD Units]*[Current Cost],"$#,###") AS [Cost $],
tblProgramHomeDepot.[Current Cost],
tblProgramHomeDepot.[Current Retail],
([YTD Units]/([Avg Inventory]*1800)) AS Turns,
Count(tblCalculatedSalesDataHomeDepot.StoreNumber) AS [Transactions
Reported],
Count((Select Distinct tblCalculatedSalesDataHomeDepot.StoreNumber From
tblCalculatedSalesDataHomeDepot)) AS Stores

FROM (tblHomeDepotPOSData INNER JOIN (tblCalculatedSalesDataHomeDepot

INNER JOIN tblProductLine ON tblCalculatedSalesDataHomeDepot.ItemNumber =
tblProductLine.ItemNumber) ON (tblHomeDepotPOSData.StoreNumber =
tblCalculatedSalesDataHomeDepot.StoreNumber) AND
(tblHomeDepotPOSData.SKUNumber =
tblCalculatedSalesDataHomeDepot.SKUNumber))

INNER JOIN tblProgramHomeDepot ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber

GROUP BY tblCalculatedSalesDataHomeDepot.SKUNumber,
tblCalculatedSalesDataHomeDepot.ItemNumber, tblProductLine.SkuDescription,
tblProgramHomeDepot.[Current Cost], tblProgramHomeDepot.[Current Retail];

*******Tables*********************************************************************
tblCalculatedSalesHomeDepot: FromDate, ToDate,
StoreNumber,SKUNumber,ItemNumber,SKUDescription... (this is a temp.
Calculated table for speed)
tblHomeDepotPOSData: ReportDate,StoreNumber,SKUNumber, ONHand
tblProductLine: ItemNumber, Description,etc
tblProgramHomeDepot: SKUNumber, ItemNumber, Cost, Retail ... linking table
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Feild could refer to more than one table? 2
Outer Join Query issues 2

Top