CODEWITHSUNDEEP
mysqldatabase
rockstardev
rockstardev

Reputation: 13527

MySQL Query to get count of unique values?

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?

Upvotes: 13

Views: 15298

Answers (4)

Thea
Thea

Reputation: 8067

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

Upvotes: 2

lc.
lc.

Reputation: 116458

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

Upvotes: 7

Lucio M. Tato
Lucio M. Tato

Reputation: 5805

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

Upvotes: 24

Dewfy
Dewfy

Reputation: 23624

You need use group by:

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

Upvotes: -2

Related Questions