MS Accesss - where cluase with group by and having clause-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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;