hej, mam problem ktorego nie moge rozwiazac. Jest sobie tabela:

[SQL] pobierz, plaintext 
[
CREATE TABLE test
 
(
  id int NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  form_id INT NOT NULL,
  sent_datetime TIMESTAMP DEFAULT NOW(),
  fill_datetime DATE DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (form_id) REFERENCES question_forms (id)
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
/sql]
 
potrzebuje wyciagnac 3 rzeczy:
1) count(id) WHERE form_id = 1
2) count(id) WHERE form_id = 1 AND fill_datetime IS NULL
3) count(id) WHERE form_id = 1 AND fill_datetime IS NOT NULL
 
do tego miejsca zapytanie nie sprawilo mi problemow. zrobilam je tak:
 
[sql]SELECT COUNT(id) AS number,
         (SELECT COUNT(id) FROM test WHERE fill_datetime
         IS NULL) AS unfilled, (SELECT COUNT(id) FROM test
         WHERE fill_datetime IS NOT NULL) AS filled FROM test
         WHERE question_form_id =1)
[SQL] pobierz, plaintext 

To zapytanie dla moich danych testowych zwraca:

[SQL] pobierz, plaintext 
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
|      4 |        3 |      1 | 
+--------+----------+--------+
[SQL] pobierz, plaintext 

Problem jest taki, ze musze to grupowac po sent_datetime. jesli dodam na koncu zapytania group by sent_datetime to dostane:

[SQL] pobierz, plaintext 
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
|      2 |        3 |      1 | 
|      1 |        3 |      1 | 
|      1 |        3 |      1 | 
+--------+----------+--------+
[SQL] pobierz, plaintext 

a powinnam dostac:

[SQL] pobierz, plaintext 
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
|      2 |        2 |      0 | 
|      1 |        1 |      0 | 
|      1 |        0 |      1 | 
+--------+----------+--------+
[SQL] pobierz, plaintext 

czyli grupowanie powinno tez byc po podzapytaniach.. czy ktos z Was moglby mi podpowiedziec jak to zrobic?

pzdr