hej, mam problem ktorego nie moge rozwiazac. Jest sobie tabela:
[
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)
To zapytanie dla moich danych testowych zwraca:
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
| 4 | 3 | 1 |
+--------+----------+--------+
Problem jest taki, ze musze to grupowac po sent_datetime. jesli dodam na koncu zapytania group by sent_datetime to dostane:
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
| 2 | 3 | 1 |
| 1 | 3 | 1 |
| 1 | 3 | 1 |
+--------+----------+--------+
a powinnam dostac:
+--------+----------+--------+
| number | unfilled | filled |
+--------+----------+--------+
| 2 | 2 | 0 |
| 1 | 1 | 0 |
| 1 | 0 | 1 |
+--------+----------+--------+
czyli grupowanie powinno tez byc po podzapytaniach.. czy ktos z Was moglby mi podpowiedziec jak to zrobic?
pzdr