CODEWITHSUNDEEP
mysqlsql
MorningSleeper
MorningSleeper

Reputation: 415

How to construct query that counts results based on column value being unique?

I have the query below which works fine but multiple records from the same member get counted in numField1. How would I change it so that numField1 only counts 1 record per member per position but still groups them as it currently does? In the table the member is in a column named memberId.

SELECT *, COUNT(field1) AS numField1, AVG(price1) FROM tableName GROUP BY field1, position HAVING status = '1' ORDER BY numField1 DESC, updated DESC

Edit: Perhaps this will help, I want to know how many records each unique pairing of field1 and position there are counting records that match by the same memberId only once.

recordId | memberId | status | updated | field1 | position | price
==================================================================
    1         55        1         1      apple       1       1.00
    2         55        1         2      apple       1        .50
    3         65        1         3      apple       1        .75
    4         75        1         4      apple       2       2.00
    5         85        1         5      apple       2       3.00
    6         95        1         6      apple       2       4.00

The expected output would be a count of 2 for apple with position 1 since only two unique memberIds contain apple and position of 1 and count of 3 for apple with position 2 since three unique membersIds contain apple with position 2. Original query counts 3 for each since it counts records with the same memberId, field1 and position multiple times. Relevant expected results below, hope this helps.

field1 | position | numField1
==============================
apple        1       2
apple        2       3

Upvotes: 1

Views: 45

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

You want one result row per field1 + position, so group by them. For each such combination you want the count of distinct memberID, so count distinct memberID.

select field1, position, count(distinct memberid)
from tablename
group by field1, position
order by field1, position;

Upvotes: 2

Pichamon
Pichamon

Reputation: 26

Can you try 'DISTINCT' ?

SELECT *
,COUNT(DISTINCT field1) AS numField1
,AVG(price1)
FROM tableName
GROUP BY field1
    ,position
HAVING STATUS = '1'
ORDER BY numField1 DESC
    ,updated DESC

Upvotes: 0

Related Questions