Group by query with count of each group under a condition

[ifiaz]

I have a table that looks like this:

DT, CONTAINERS, FLT_TYPE, ...
01/01/05, 100, I
01/01/05, 100, I
01/01/05, 200, D
02/01/05, 100, I
02/01/05, 1, D
02/01/05, 2, D
....
....

I want a summary query that shows
the date (group), Sum of Containers for that day, Count of 'I' for that
day
01/01/05, 400, 2
02/01/05, 103, 1
....
....

I tried various ways in vain. I managed to accomplish it using VBA
custom function. Is there an efficent way by just using a single query.
Thanks.

 

[JohnFol]

Something like

select DT, SUM(CONTAINERS) as SumOfContainers, Count(FLT_TYPE) As
CountOfFLT_TYPE from Mytable group by DT

 

[Chris2]

I have a table that looks like this:

DT, CONTAINERS, FLT_TYPE, ...
01/01/05, 100, I
01/01/05, 100, I
01/01/05, 200, D
02/01/05, 100, I
02/01/05, 1, D
02/01/05, 2, D
...
...

I want a summary query that shows
the date (group), Sum of Containers for that day, Count of 'I' for that
day
01/01/05, 400, 2
02/01/05, 103, 1
...
...

I tried various ways in vain. I managed to accomplish it using VBA
custom function. Is there an efficent way by just using a single query.
Thanks.

CREATE TABLE YourTable_01272005_1
(YourTableID AUTOINCREMENT

,DT DATETIME NOT NULL
,CONTAINERS LONG NOT NULL
,FLT_TYPE TEXT(1) NOT NULL
)





SELECT Y1.DT

,SUM(Y1.CONTAINERS)

,COUNT(IIF(Y1.FLT_TYPE = "I", Y1.FLT_TYPE, Null))

FROM YourTable_01272005_1 AS Y1

GROUP BY Y1.DT


The results:



01/01/05, 400, 2
02/01/05, 103, 1