Counting elements conditionally and unconditionally

[neo]

Dear MSAccess Group users,

A small trivial SQL question. I have a dataset which has three columns:

Date Element Packets

There are multiple elements for each date for which I have packet
measurements.

I want to create one query which:
- groups by Date
- counts the number of elements for each day
- counts the number of elements each day which have a packet count
greater than some threshold

So, three columns as output:
Date (Number of Elements) (Number of Elements with Packet>Threshold)

How can I do this in SQL ?
Thanks,
Neo

 

[Douglas J. Steele]

I'm not sure you can do it with a single query. You might need to have
multiple queries, and join them together.

Try creating a query qryDistinctElements as:

SELECT DISTINCT [Date], Element
FROM MyTable

Try creating a second query qryDistinctElementsThreshold as:

SELECT DISTINCT [Date], Element
FROM MyTable
WHERE Packets > 100

You can then get the counts of each as:

SELECT [Date], Count(*) As [Number Of Elements]
FROM qryDistinctElements
GROUP BY [Date]

and

SELECT [Date], Count(*) As [Number of Elements with Packet>Threshold]
FROM qryDistinctElementsThreshold
GROUP BY [Date]

You could join that second pair of queries together to give you the desired
single query.


Note: you really shouldn't use Date as a field name: it's a reserved word.

 

[neo]

Many thanks Doug. Will change the date field. I had the two query thing
going but wanted to simply things a bit which is why I was wondering if
there was a way to do it with one query. Ok thanks