Database table count same value

Question

1.00/5 (1 vote)

See more:

I have a database table which users id had a foreign id of another user like:

id | refer_id
1.......98
2.......7
3.......4
4.......98
5.......87
6.......13
7.......4
8.......4

I want to check the whole table and count that there's 2(98),1(7),3(4),1(13) and so on.
I am new to database and confuse how the query going to work.

Posted 15-Apr-13 11:14am

Yafa Su

Add a Solution

Comments

DinoRondelly 15-Apr-13 17:23pm

What have you tried?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-04-15T11:25:00

Solution 1

Try this:

SQL

SELECT refer_id, COUNT(refer_id) AS CountOfReferId
FROM YourTable
GROUP BY refer_id
ORDER BY refer_id

More about: Aggregate functions - MySQL 5.0 Reference[^]
MySQL aggregate functions[^]

To get refer_id's where COUNT(refer_id)>=4

SQL

SELECT refer_id, COUNT(refer_id)
FROM Table1
GROUP BY refer_id
HAVING COUNT(refer_id)>=4

To get SUM(above)

SQL

SELECT SUM(T.CountOfReferId) AS SumOfCountReferId_4
FROM (
    SELECT refer_id, COUNT(refer_id) AS CountOfReferId
    FROM Table1
    GROUP BY refer_id) AS T
WHERE T.CountOfReferId >=4

Posted 15-Apr-13 11:25am

Maciej Los

Updated 16-Apr-13 5:38am

v3

Comments

Richard C Bishop 15-Apr-13 17:30pm

Nice and simple.

Maciej Los 15-Apr-13 17:31pm

Thank you, richcb ;)

Yafa Su 16-Apr-13 9:33am

sorry for the late reply, I will give a try

Yafa Su 16-Apr-13 9:39am

Nice thanks it's what I am looking for :)

Maciej Los 16-Apr-13 9:57am

You're welcome ;)

Yafa Su 16-Apr-13 10:20am

I have another question is it possible to only get people that had let say 4 refer_id and sum all these people that had 4 refer_id? like 50 people had 4 refer_id

Maciej Los 16-Apr-13 11:39am

Yes, it's possible. See my updated answer.
Follow the links (in answer) to get more examples.

Yafa Su 16-Apr-13 12:31pm

Thanks so much was solving this issue for day. :)
Also thank you for those links they really help me.

Yafa Su 16-Apr-13 16:13pm

Can you make me a last favor? your sql query work perfect. I was trying to play with your last query to display all instead of display the one want. But I can't make it to work.

Yafa Su 16-Apr-13 16:16pm

Like with your last query I have to write lot of query in my php to retrieve want I want. I want to display into 1 table with 1 query possible?

Yafa Su 16-Apr-13 16:22pm

display like the first but with information I want for the last query

Maciej Los 16-Apr-13 16:33pm

I'm not sure what you want...
Please, show me expected output.

Yafa Su 16-Apr-13 16:36pm

Like in the last query was what I want. But I have to write at the end 'WHERE T.CountOfReferId =Xnumber'. Is there a way too get the whole table instead of 1 row?

Yafa Su 16-Apr-13 16:43pm

Let say 'WHERE T.CountOfReferId = 4', I will get 12 from the table above. But only display that specific row. possible to show all? like 4 | 12, 98 | 196, 7 | 7....

Maciej Los 16-Apr-13 16:51pm

http://stackoverflow.com/questions/7869676/parameterized-queries-php-mysql[^]