GROUP BY not counting 0's

  • Thread starter Thread starter James B
  • Start date Start date
J

James B

I asked a similiar question before but it had a INNER
JOIN and the answer given was specific to the JOIN, now
this query doens't have a JOIN and its doing the same
thing

SELECT ProductId, SUM(count) as sum_count
FROM Sales
WHERE date BETWEEN #start# AND #end#
GROUP BY ProductId

For example if product ids 1-10 exist in the data and if
no sales for product id 3 exists from start to end then
the query won't return a record with product id = 3 and
sum_count = 0, it simply leaves it out.

How can I get this query to return all Product Ids which
exist in the data, even if they don't have Sales between
start and end?
 
Not sure if this will do the trick but try this:

SELECT ProductId, SUM(Nz([count],0)) as sum_count
FROM Sales
WHERE date BETWEEN #start# AND #end#
GROUP BY ProductId
 
That doesn't work, it doesn't recognize Nz. I'm using
ASP on WinXP. Even if it did work I'm not sure it would
do the right thing as the sum isn't null, it simply
doesn't exist for some of the ProductIds, maybe this is
NULL though internally to SQL, thanks for the suggestion,
any more?
 
Ah...you didn't say ASP.

Try this:

SELECT ProductId, SUM(IIf([count] Is Null,0,[count])) as sum_count
FROM Sales
WHERE date BETWEEN #start# AND #end#
GROUP BY ProductId;
 
Or

SELECT ProductId, SUM(IIf(IsNull([count]),0,[count])) as sum_count
FROM Sales
WHERE date BETWEEN #start# AND #end#
GROUP BY ProductId;

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Ah...you didn't say ASP.

Try this:

SELECT ProductId, SUM(IIf([count] Is Null,0,[count])) as sum_count
FROM Sales
WHERE date BETWEEN #start# AND #end#
GROUP BY ProductId;


--

Ken Snell
<MS ACCESS MVP>


That doesn't work, it doesn't recognize Nz. I'm using
ASP on WinXP. Even if it did work I'm not sure it would
do the right thing as the sum isn't null, it simply
doesn't exist for some of the ProductIds, maybe this is
NULL though internally to SQL, thanks for the suggestion,
any more?
Click to expand...
Click to expand...
 
Do you have a PRODUCTS table with a ProductID? Then I think that Something like
the following might work

SELECT P.ProductID,
IIF(Sum(S.Count) Is Null, 0, Sum(S.Count))as SumCount
FROM Products as P LEFT JOIN Sales as S
ON P.ProductID = S.ProductID
WHERE S.[Date] Between #Start# and #End# OR
S.ProductID is Null
GROUP BY P.ProductID
 
-----Original Message-----
Do you have a PRODUCTS table with a ProductID? Then I think that Something like
the following might work
Click to expand...

yes I think that would work, but unfortunately my
products table with all product ids is in another mdb.
thanks for the suggestion.
 
Arg, thanks a lot of your help, but still the same
results, I also tried the following

SELECT ProductId, IIf(IsNull(SUM(count)),0,SUM(count)) as
sum_count ... rest the same

and still the same results, I think the problem is not
the fact that the sum is null or 0, but that there are no
instances of the missing ProductIds in the rows that meet
the WHERE clause.

My next thought is I need to use a compound SELECT
statement. Where I get all unique ProductIds in the
whole date range, then do the SELECT as before, but
setting ProductIds sums to 0 where they aren't found. I
have never done a compound or double SELECT though, I
have seen them, if you could suggest a SELECT like this
that would be great, or maybe it won't work, I'm sure you
know more than I.

OR I have a list of products ids I know should be in the
results (this is retrieved from another mdb). Is there a
way to build IF or CASE logic in the SQL result which
basically says 'if ProductId=N not found then sum=0'?
 
This is roundabout way for the moment, but let's see if it works and then go
from there to make better:

SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "#") AS sum_count
FROM Sales;
 
-----Original Message-----
SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "#") AS sum_count
FROM Sales;
Click to expand...

Using this query outputs a _bunch_ of rows, all with the
same sum_count, it is summing all ProductIds together.

Then I tacked on a GROUP BY ProductId to the end and it
now only outputs the number of rows as there are number
of ProductIds in the data, which is good, but all the
sums_counts are the total sum_counts of all ProductIds
together. So its summing all counts, then grouping them
together by ProductId.


Is it possible to do something like this? This is psuedo
SQL...

SELECT ProductId, SUM(count) as sum_count FROM Sales
WHERE ...datefilter... AND ProductId=[results from SELECT
DISTINCT ProductId FROM Sales];


So for example if SELECT DISTINCT ProductId FROM Sales
returned [1,2,3,4,5]

it would return a result where each row would in effect
have the following value.
SELECT ... FROM SALES WHERE ... AND ProductId=1;
SELECT ... FROM SALES WHERE ... AND ProductId=2;
SELECT ... FROM SALES WHERE ... AND ProductId=3;
SELECT ... FROM SALES WHERE ... AND ProductId=4;
SELECT ... FROM SALES WHERE ... AND ProductId=5;

Maybe this isn't even possible, but is close I can think
of to what a compound SELECT might look like.
 
Oops...missed including the ProductID criterion:

SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "# AND ProductID="
& ProductID) AS sum_count
FROM Sales;


--

Ken Snell
<MS ACCESS MVP>

James B said:
-----Original Message-----
SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "#") AS sum_count
FROM Sales;
Click to expand...

Using this query outputs a _bunch_ of rows, all with the
same sum_count, it is summing all ProductIds together.

Then I tacked on a GROUP BY ProductId to the end and it
now only outputs the number of rows as there are number
of ProductIds in the data, which is good, but all the
sums_counts are the total sum_counts of all ProductIds
together. So its summing all counts, then grouping them
together by ProductId.


Is it possible to do something like this? This is psuedo
SQL...

SELECT ProductId, SUM(count) as sum_count FROM Sales
WHERE ...datefilter... AND ProductId=[results from SELECT
DISTINCT ProductId FROM Sales];


So for example if SELECT DISTINCT ProductId FROM Sales
returned [1,2,3,4,5]

it would return a result where each row would in effect
have the following value.
SELECT ... FROM SALES WHERE ... AND ProductId=1;
SELECT ... FROM SALES WHERE ... AND ProductId=2;
SELECT ... FROM SALES WHERE ... AND ProductId=3;
SELECT ... FROM SALES WHERE ... AND ProductId=4;
SELECT ... FROM SALES WHERE ... AND ProductId=5;

Maybe this isn't even possible, but is close I can think
of to what a compound SELECT might look like.
Click to expand...
 
Here is the query I ran

"SELECT ProductId, DSum(""count"", ""Sales"", ""date BETWEEN #"& start & "#
AND #" & end & "# AND ProductId=ProductId"") AS sum_count FROM Sales;"

Prints out a ton of rows same as before, also added the GROUP BY ProductId
to the end and same as before, prints grand totals for all ProductIds.

If intead you meant the ProductId should be an ASP variable in the query
above, then the query has to be made for each product id. THis is how I
started the query and its very in-effiecint and the whole reason I started
using the GROUP BY :)
 
OK - I think you're going to have to use a second table / query in a join in
order to get the list of all existing ProductIDs and do a left join to Sales
based on that. I assume that you have a table containing all possible
ProductIDs? Assuming it's called tblProducts, this query may get you your
desired results:

SQLString = "SELECT Products.ProductID, " & _
"Sum(IIf(IsNull(Sales.[count]),0,Sales.[count]) " & _
"AS sum_count FROM " & _
"Products LEFT JOIN Sales ON " & _
"Products.ProductID = Sales.ProductID " & _
"WHERE Sales.[date] BETWEEN #" & _
start & "# AND #" & end & "#;"
--

Ken Snell
<MS ACCESS MVP>

James B said:
Here is the query I ran

"SELECT ProductId, DSum(""count"", ""Sales"", ""date BETWEEN #"& start & "#
AND #" & end & "# AND ProductId=ProductId"") AS sum_count FROM Sales;"

Prints out a ton of rows same as before, also added the GROUP BY ProductId
to the end and same as before, prints grand totals for all ProductIds.

If intead you meant the ProductId should be an ASP variable in the query
above, then the query has to be made for each product id. THis is how I
started the query and its very in-effiecint and the whole reason I started
using the GROUP BY :)


Ken Snell said:
Oops...missed including the ProductID criterion:

SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "# AND ProductID="
& ProductID) AS sum_count
FROM Sales;
Click to expand...
Click to expand...
 
< sigh >.... somedays one should just not get out of bed....

After telling you I would assume that the table is named tblProducts, I then
post an example that uses a different table name... Let's try this:

SQLString = "SELECT tblProducts.ProductID, " & _
"Sum(IIf(IsNull(Sales.[count]),0,Sales.[count]) " & _
"AS sum_count FROM " & _
"tblProducts LEFT JOIN Sales ON " & _
"tblProducts.ProductID = Sales.ProductID " & _
"WHERE Sales.[date] BETWEEN #" & _
start & "# AND #" & end & "#;"


--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
OK - I think you're going to have to use a second table / query in a join in
order to get the list of all existing ProductIDs and do a left join to Sales
based on that. I assume that you have a table containing all possible
ProductIDs? Assuming it's called tblProducts, this query may get you your
desired results:

SQLString = "SELECT Products.ProductID, " & _
"Sum(IIf(IsNull(Sales.[count]),0,Sales.[count]) " & _
"AS sum_count FROM " & _
"Products LEFT JOIN Sales ON " & _
"Products.ProductID = Sales.ProductID " & _
"WHERE Sales.[date] BETWEEN #" & _
start & "# AND #" & end & "#;"
--

Ken Snell
<MS ACCESS MVP>

James B said:
Here is the query I ran

"SELECT ProductId, DSum(""count"", ""Sales"", ""date BETWEEN #"& start & "#
AND #" & end & "# AND ProductId=ProductId"") AS sum_count FROM Sales;"

Prints out a ton of rows same as before, also added the GROUP BY ProductId
to the end and same as before, prints grand totals for all ProductIds.

If intead you meant the ProductId should be an ASP variable in the query
above, then the query has to be made for each product id. THis is how I
started the query and its very in-effiecint and the whole reason I started
using the GROUP BY :)


Ken Snell said:
Oops...missed including the ProductID criterion:

SELECT ProductId,
DSum("count", "Sales", "[date] BETWEEN #" &
start & "# AND #" & end & "# AND ProductID="
& ProductID) AS sum_count
FROM Sales;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top