Using SubQuery to find 0 Sales Stores

[lazyme]

I am trying to learn subquery can can't wrap my head around this one and hoping someone can show me.

Say a table has these fields:
1. Week
2. Item
3. Store Number
4. Sales

How should I write a subquery to count the number of stores the have 0 sales for each particular item?

 

[bob fitz]

I'm not sure that you need a subquery for that.
I haven't tested a solution, but I think that can be done with a Select Query and adding a Group By

 

[lazyme]

Well, can you venture an actual solution?

 

[bob fitz]

I may be able to if you can tell us the name of your table.

 

[lazyme]

Let's call it [tbl_TEMP]

 

[bob fitz]

You may like to try using the code below as the SQL statement.

I have changed the name of the field "Week" to "WeekNum" because I think that Week is a reserved word in Access.

SELECT tbl_TEMP.WeekNum, tbl_TEMP.Item, tbl_TEMP.[Store Number]
FROM tbl_TEMP
WHERE (((tbl_TEMP.Sales)=0))
GROUP BY tbl_TEMP.WeekNum, tbl_TEMP.Item, tbl_TEMP.[Store Number];

Please let us know how you get on.

 

[lazyme]

bob_fitz, just by looking at your code, the query will only list the stores with 0 sales but not count the number of stores. Even if you change it to COUNT for [Stores], it will only count the total number of stores. That's why I need to use SubQuery.

 

[bob fitz]

Sorry. I didn't understand the requirement correctly.

If nobody gives you an answer I will have another try tomorrow. (With SubQuery )

 

[bob fitz]

Hi lazyme

I don't have much experience with subqueries. I've had a look at this again tonight but I have not, as yet, come up with a subquery solution for you.

I have been able to get the result that you need (I think) by using a query that queries a query. The SQL's of the two queries needed are shown below.

SELECT tbl_TEMP.[Store Number], tbl_TEMP.Sales 
FROM tbl_TEMP 
WHERE (((tbl_TEMP.Sales)=0));

SELECT Count(TEMP_ZeroSales.[Store Number]) AS [CountOfStore Number], TEMP_ZeroSales.WeekNum, TEMP_ZeroSales.Item
FROM TEMP_ZeroSales
GROUP BY TEMP_ZeroSales.WeekNum, TEMP_ZeroSales.Item;

 

[lazyme]

bob_fitz, yes, you can do that. But my point is to learn how to write it as a SubQuery.

 

[bob fitz]

Yes, I thought that was the case. Just posted the 2 query solution in case that was of use.

If you haven't already seen it, Allen Browne has some info at:

http://allenbrowne.com/subquery-01.html

I'd rather like to find the Subquery solution myself now.

When you find the solution, please post back with it.

 

[bob fitz]

I am pleased to tell you that I now have a better understanding of using s SubQuery for this situation. At least I think I have.

The SQL statement below returns the same values as the two query solution that I posted yesterday but uses a subquery instead of a second query.

SELECT Item, Count([Store Number]) AS [CountOfStore Number]
FROM (SELECT tbl_TEMP.WeekNum, tbl_TEMP.Item, tbl_TEMP.[Store Number], tbl_TEMP.Sales
FROM tbl_TEMP
WHERE (((tbl_TEMP.Sales)=0))) AS AA
GROUP BY tbl_TEMP.Item;

The SQL statement used in the 1st query of the two query solution is inserted into the SQL statement of the 2nd query as the FROM clause in place of the first query’s name. The “;” at the end of the 1st query’s SQL must be removed and the whole of the statement then enclosed in brackets (SELECT etc, etc… ). An alias name for the subquery is then added AS AA

Is this what you were looking for?