MySQL Query to get count of unique values?

Question

Hits Table:

hid | lid | IP
1   | 1   | 123.123.123.123
2   | 1   | 123.123.123.123
3   | 2   | 123.123.123.123
4   | 2   | 123.123.123.123
5   | 2   | 123.123.123.124
6   | 2   | 123.123.123.124
7   | 3   | 123.123.123.124
8   | 3   | 123.123.123.124
9   | 3   | 123.123.123.124

As you can see, there following are the unique hits for the various lid:

lid 1: 1 unique hit
lid 2: 2 unique hits
lid 3: 1 unique hit

So basically, I need a query that will return the following:

lid | uhits |
1   | 1     |
2   | 2     |
3   | 1     |

Anybody know how to get that?

Answer 1

You need use group by:

SELECT lid, count(*)
    FROM Table
    GROUP BY lid

Answer 2

Select lid, count(distinct IP) as uhits
from hits
group by lid

Answer 3

Until you start getting into really complicated queries, SQL is made so it reads quite like a natural sentence. So first, if you can describe exactly what you want out of your query, you've already half written the SQL.

In this case, you can describe your problem like:

Get lid and the aggregate count of unique IP from my table for each lid.

The only thing that remains is to translate this, using SQL keywords. The important ones here being:

• get -> SELECT
• count -> COUNT
• unique -> DISTINCT
• aggregate..for each <field> -> SELECT <aggregate function>..GROUP BY <field>

So, your sentence above starts to look like:

SELECT lid and the aggregate COUNT of DISTINCT IP FROM my table GROUP BY lid.

Removing unnecessary words, and cleaning it up to use SQL syntax leaves the final query:

SELECT hits.lid, COUNT(DISTINCT hits.IP) AS uhits
FROM hits
GROUP BY hits.lid

Answer 4

SELECT lid, COUNT(DISTINCT IP)  
FROM hits  
GROUP BY lid