mysql - COUNT(*) return wrong number -

mysql - COUNT(*) return wrong number -

it seems don't something. please consider query

select count(*) `numrows` (`exp_channel_titles` ch) bring together `exp_channel_data` cd on `cd`.`entry_id`=`ch`.`entry_id` left bring together `exp_matrix_data` md on `md`.`entry_id`=`ch`.`entry_id` , field_id = 14 left bring together `exp_assessment_users` au on `au`.`entry_id`=`ch`.`entry_id` ch.channel_id = 4 grouping `ch`.`entry_id`

it returns 2

but if alter

select * (`exp_channel_titles` ch) bring together `exp_channel_data` cd on `cd`.`entry_id`=`ch`.`entry_id` left bring together `exp_matrix_data` md on `md`.`entry_id`=`ch`.`entry_id` , field_id = 14 left bring together `exp_assessment_users` au on `au`.`entry_id`=`ch`.`entry_id` ch.channel_id = 4 grouping `ch`.`entry_id`

result 1 row only. how so?

you're grouping, means internally matching rows collapsed single entity. e.g. consider false table this:

field ----- a

yes, 1 field table, 2 records, both of have value a in them.

select * table grouping field

group by find fields have same value, , collapse them downwards single record, 2 records of a become 1 row in result set, , end with

field -----

but doing

select count(*) table grouping field

changes things. db literally count how many records collapsed downwards single row of result set. still single row in result set, contains count of how many rows collapsed group by:

count(*) -------- 2

one row, value of 2, because there 2 rows a.

now if had table more records:

field ----- a b c c c

you get:

select * ... grouping field field ----- b c select count(*), field ... grouping field count(*) field ---------------- 2 1 b 3 c

again, 3 rows of results, note how count represents how many of each grouped field there in original table.

mysql