SQL with count and where

[terrygtx]

I couldn't find an answer from searching - its hard to craft a search with these items (SQL, count, where, zero) and not get a billion hits.

If I use the following query, I get a count by room (from qryPriRoomCode) of the total number of messages associated with that room even if the number is zero.

SELECT DISTINCT qryPriRoomCode.PriRoomCode, Count(qrySyslogRoomMatch.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode LEFT JOIN qrySyslogRoomMatch ON qryPriRoomCode.PriRoomCode = qrySyslogRoomMatch.RoomCode
GROUP BY qryPriRoomCode.PriRoomCode;

But, if I add a where clause to limit to a certain date and time range, I no longer get a 'LEFT JOIN' result. In other words, the messages are limited to the right date/time, but in the process, all zero results are thrown out:

SELECT DISTINCT qryPriRoomCode.PriRoomCode, Count(qrySyslogRoomMatch.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode LEFT JOIN qrySyslogRoomMatch ON qryPriRoomCode.PriRoomCode=qrySyslogRoomMatch.RoomCode
WHERE (((qrySyslogRoomMatch.MsgDate)=Date()) AND ((qrySyslogRoomMatch.MsgTime)>DateAdd("h",-1,Time())))
GROUP BY qryPriRoomCode.PriRoomCode;


I think I know why this is happening - I just don't know what a good solution would be. I want the results to show the number of messages per room received in the last hour even if that number is zero.

Any help is appreciated,

t

 

[MSAccessRookie]

I couldn't find an answer from searching - its hard to craft a search with these items (SQL, count, where, zero) and not get a billion hits.

If I use the following query, I get a count by room (from qryPriRoomCode) of the total number of messages associated with that room even if the number is zero.

SELECT DISTINCT qryPriRoomCode.PriRoomCode, Count(qrySyslogRoomMatch.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode LEFT JOIN qrySyslogRoomMatch ON qryPriRoomCode.PriRoomCode = qrySyslogRoomMatch.RoomCode
GROUP BY qryPriRoomCode.PriRoomCode;

But, if I add a where clause to limit to a certain date and time range, I no longer get a 'LEFT JOIN' result. In other words, the messages are limited to the right date/time, but in the process, all zero results are thrown out:

SELECT DISTINCT qryPriRoomCode.PriRoomCode, Count(qrySyslogRoomMatch.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode LEFT JOIN qrySyslogRoomMatch ON qryPriRoomCode.PriRoomCode=qrySyslogRoomMatch.RoomCode
WHERE (((qrySyslogRoomMatch.MsgDate)=Date()) AND ((qrySyslogRoomMatch.MsgTime)>DateAdd("h",-1,Time())))
GROUP BY qryPriRoomCode.PriRoomCode;


I think I know why this is happening - I just don't know what a good solution would be. I want the results to show the number of messages per room received in the last hour even if that number is zero.

Any help is appreciated,

t

A WHERE Clause affects the DataSet Before the Grouping occurs. A HAVING Clause will affect the DataSet After the Grouping Occurs. Try a HAVING Clause and see if it gets you what you want.

SELECT DISTINCT qryPriRoomCode.PriRoomCode, 
    Count(qrySyslogRoomMatch.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode LEFT JOIN qrySyslogRoomMatch 
    ON qryPriRoomCode.PriRoomCode=qrySyslogRoomMatch.RoomCode
GROUP BY qryPriRoomCode.PriRoomCode
[B][COLOR=#008000]HAVING[/COLOR][/B] ((qrySyslogRoomMatch.MsgDate=Date()) AND
    (qrySyslogRoomMatch.MsgTime>DateAdd("h",-1,Time())));

 

[terrygtx]

Thank you for the prompt reply.

When I tried just as you entered, Access complains about:

"... execute a query that does not include the specified expression '(qrySyslogRoomMatch.MsgDate)=Date()) AND (qrySyslogRoomMatch.MsgTime)>DateAdd("h",-1,Time()) as part of an aggregate function.'"

But if I add those fields directly to the SELECT or to the GROUP BY, then it ruins the groupings.

---

Any ideas?

t

 

[MSAccessRookie]

Thank you for the prompt reply.

When I tried just as you entered, Access complains about:

"... execute a query that does not include the specified expression '(qrySyslogRoomMatch.MsgDate)=Date()) AND (qrySyslogRoomMatch.MsgTime)>DateAdd("h",-1,Time()) as part of an aggregate function.'"

But if I add those fields directly to the SELECT or to the GROUP BY, then it ruins the groupings.

---

Any ideas?

t

Sorry that did not work. I will have to defer to others for additional advice at this time.

 

[SimonB1978]

Hi,

have you tried that:

SELECT qryPriRoomCode.PriRoomCode, Count(my_query.MsgUnique) AS CountOfMsgUnique
FROM qryPriRoomCode
LEFT JOIN (SELECT * FROM qrySyslogRoomMatch WHERE qrySyslogRoomMatch.MsgDate=Date() AND qrySyslogRoomMatch.MsgTime>DateAdd("h",-1,Time())) my_query
ON qryPriRoomCode.PriRoomCode=my_query.RoomCode
GROUP BY qryPriRoomCode.PriRoomCode;

That should keep your LEFT JOIN "right"

I think that's what you want to achieve.

Simon B.

 

[terrygtx]

Hi,

have you tried that:
...
I think that's what you want to achieve.

Simon B.

Thanks Simon - that did it perfectly. I was hoping for a non-nested solution - but I figured that it would likely have to be nested. I've been programming for a long time, but I still can't seem to wrap my head completely around complex SQL statements. Single layered SQL is ok, but once they start getting nested, I just have not yet gotten it.

Thanks again,

t

 

[SimonB1978]

You're welcome!

Simon B.