CODEWITHSUNDEEP
mysql
Safoor Safdar
Safoor Safdar

Reputation: 5696

Mysql Query returning record count is more than returning rows

Mysql query giving only one record but the count function giving count more than one.

[edit] My basic aim to get count of user who attempted the survey. and in table each row represent the answer of each question in survey. so each row contain user id.

Database table Schema(table):

+-------------+------------------+------+-----+---------------------+----------------+
| Field       | Type             | Null | Key | Default             | Extra          |
+-------------+------------------+------+-----+---------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| survey_id   | int(10) unsigned | NO   |     | NULL                |                |
| user_id     | int(10) unsigned | NO   |     | NULL                |                |
+-------------+------------------+------+-----+---------------------+----------------+

Data in Table(table):

+----+-----------+---------+
| id | survey_id | user_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       1 |
|  3 |         1 |       1 |
+----+-----------+---------+
3 rows in set (0.00 sec)

COUNT Query :

SELECT count(*) AS total FROM `table` WHERE survey_id = 1 GROUP BY user_id

Result:

+-------+
| total |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

And Simple query:

SELECT id,user_id,survey_id AS total FROM `table` WHERE survey_id = 1 GROUP BY user_id

Result:

+----+---------+-------+
| id | user_id | total |
+----+---------+-------+
|  1 |       1 |     1 |
+----+---------+-------+
1 row in set (0.00 sec)

Desire Result:

The count and number of row(s) should be same.

so, why second query returning single row and first count query returning not one.

let me know if still unclear anything.

Any help would be appreciable in advance.

Upvotes: 2

Views: 3070

Answers (1)

Benedikt
Benedikt

Reputation: 607

If you want to count the number of groups GROUP BY returns you can use COUNT(distinct user_id)

Upvotes: 2

Related Questions