MySQL Query : counting in 1 column,grouping by another

[angor]

Hello!
Please help!
I need to make a SQL-query in Access DataBase.

I have a table with the fields
1.Date
2.Bool

e.g.:

Date | Bool

1.09.05 | Yes
1.09.05 | Yes
1.09.05 | No
2.09.05 | No
2.09.05 | Yes
3.09.05 | No
3.09.05 | No
5.09.05 | No



and i need to generate the following one:
Date | Quantity

1.09.05 | 2
2.09.05 | 1
3.09.05 | 0
5.09.05 | 0

where the field "Quantity" is the quantity of "Yes" on tha date.

I would be very thankful if u could help me with that!
my email: naukma@gmail.com

Andrew.

 

[LittleSmudge]

What you need is something like

SELECT DateField, Count(DateField) As Quantity
FROM TableName
WHERE Bool
GROUP BY DateField

Replace field and table names as appropriate.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[angor]

I am afraid i have already tried that - but in the result table it does not contain strings where only 'NO's are...
so it's like this:
1.09.05 | 2
2.09.05 | 1

instead of

1.09.05 | 2
2.09.05 | 1
3.09.05 | 0
5.09.05 | 0

...

 

[PHV]

If Bool is defined as Boolean:
SELECT DateField, Sum(Abs(Bool)) As Quantity
FROM TableName
GROUP BY DateField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[angor]

NO!Bool is just the name of a field, let it be just 'Answer'

so

Date | Answer

.. | ..

 

[PHV]

SELECT DateField, Sum(IIf(Bool='Yes',1,0)) As Quantity
FROM TableName
GROUP BY DateField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LittleSmudge]

Or

SELECT DL.DateField , Nz(Quantity ,0)
FORM ( SELECT DISTINCT DateField FROM TableName ) As DL
LEFT JOIN (SELECT DateField, Count(DateField) As Quantity
           FROM TableName
           WHERE Bool
           GROUP BY DateField 
          ) As Q
     ON DL.DateField = Q.DateField

'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[angor]

thanx!!!last 2 examples both work perfect!