SQL: Filter by count in a table with NO primary key

44
September 22, 2018, at 8:50 PM

In a table which looks like::

  name    |    posts    |  comments
---------------------------------------
user1     |  page1      |  90
user1     |  page2      |  50
user1     |  page3      |  40
user2     |  page2      |  55
user2     |  page4      |  45
.         |  .          |  .
.         |  .          |  .
.         |  .          |  .

What's the best way to, for example, find the users which have more than 2 posts?

The way I'd normally do this is to group by using the primary key,

(for example) SELECT * FROM table
              GROUP BY PrimaryKey
              HAVING COUNT(name) > 2

But can't do this in this case;; what's a way to proceed?

Thank you in advance!

Answer 1

You seem to want this:

SELECT user_name
FROM table
GROUP BY user_name
HAVING COUNT(*) > 2;

You seem to have a misunderstanding of what a primary key is. Your version of the query will return no rows. Why not? A primary key is unique. There are no duplicates. You want duplicates by user_name, so this seems like the appropriate query.

READ ALSO
how to number a resultset from MySQL (another option to not use user variables)

how to number a resultset from MySQL (another option to not use user variables)

For example a create this query to get all the users and their posts asociated

27
how to add “00” or “+” symbol in front of column values of phone code in MySQL

how to add “00” or “+” symbol in front of column values of phone code in MySQL

I have a table contains 239 records; each record includes three columns: "id, country name, phone code "I need to add a "00" or "+" symbol in front of each value of phone code

45
Generate a report for multiple locations

Generate a report for multiple locations

I am attempting to achieve the following result in CodeIgniter

32
Requesting help in reading blob datatype in mariadb via python

Requesting help in reading blob datatype in mariadb via python

I am trying to print the content inside blob data type and here is my python code and as well the error

49