CODEWITHSUNDEEP
ms-accessgroup-bywhere-clause
John
John

Reputation: 2852

MS Accesss - where cluase with group by and having clause

Following is the part of data, in Access:

icode   soldQty rackQty      dt
---------------------------------------
14000   10      50       03/17/22 20:35
15000    1      45       03/17/22 15:35
16000    3      55       03/17/22 08:22
14000   30      48       03/18/22 14:05
15000   18      62       03/17/22 13:35
16000    3      47       03/17/22 15:23
14000    1      49       03/19/22 16:35
17000    1      49       03/17/22 15:13
14000   24      26       03/17/22 10:35
15000   10      33       03/17/22 20:37

There are more than 100 icodes, I am putting just 3 as an example. I want to generate a Weekly (or between some dates) report based on a certain time period. Say, a weekly report between 10:00 to 17:00 for 3 items, 14000, 15000 and 16000. The output, I am expecting is:

icode   soldQty rackQty
14000   54         74
15000   19        107
16000    4         96

Using this query, I am able to get the sum.

select icode, sum(soldQty), sum(rackQty) from sales 
group by icode having icode between 14000 and 16000 
order by icode

I am confused where to put the where clause so that I can have a condition for the timing (10:00 to 17:00) constraint?

Upvotes: 0

Views: 60

Answers (2)

forpas
forpas

Reputation: 164139

All the conditions should be placed in the WHERE clause.

The HAVING clause is used for conditions that contain aggregated values like SUM() or COUNT() and it is processed after the aggregation:

SELECT icode, 
       SUM(soldQty) AS total_soldQty, 
       SUM(rackQty) AS total_rackQty 
FROM sales 
WHERE icode BETWEEN 14000 AND 16000
  AND TimeValue(dt) BETWEEN #10:00:00# AND #17:00:00#
GROUP BY icode  
ORDER BY icode;

Upvotes: 2

Gustav
Gustav

Reputation: 55921

Try this:

select icode, sum(soldQty), sum(rackQty) 
from sales 
where TimeValue(dt) between #10:00:00# and #17:00:00#
group by icode 
having icode between 14000 and 16000 
order by icode

Upvotes: 1

Related Questions