query SELECT COUNT only unique values

Question

I'm using this code to count the rows in selected table

$queryvps = "SELECT COUNT(ID) FROM vpss";  
$resultvps = mysql_query($queryvps) or die(mysql_error()); 
foreach(mysql_fetch_array($resultvps) as $vpscount);

I need to count results from another table but they are not unique, how to count duplicate results once?

*"I need to count results from another table but they are not unique"* - what "other" table? Your question is unclear and stands at being closed because of it. — Funk Forty Niner, Jun 16 '15 at 18:41
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 16 '15 at 18:46
Above are most likely not being viewed *Sam* - @JayBlanchard - posted mine 7 mins. prior to this; waste of time, I think. — Funk Forty Niner, Jun 16 '15 at 18:47
*"I need to count results from **another table**"* - explain that, if you're bothering looking at the comments here. — Funk Forty Niner, Jun 16 '15 at 18:49
I need to count results from Coulmn under name Username. But most of them are not unique. example: http://gyazo.com/cec6b0c3510b563dbc353345f904cd86 — CEED, Jun 16 '15 at 18:52

MiiinimalLogic · Accepted Answer · 2015-06-16T19:01:43.503

1

Use the keyword DISTINCT in your query. For example:

SELECT COUNT(DISTINCT(Username)) FROM vpss;

edited Jun 16 '15 at 19:01

answered Jun 16 '15 at 18:39

MiiinimalLogic

820
6
11

Prints "1" Here is what kind of data i store in my Table http://gyazo.com/a3b47f044a9a865527e34b256292a02a – CEED Jun 16 '15 at 18:45
What was your query? – MiiinimalLogic Jun 16 '15 at 18:46
$querytotal = "SELECT DISTINCT(ID) FROM vpss"; $resulttotal = mysql_query($querytotal) or die(mysql_error()); foreach(mysql_fetch_array($resulttotal) as $usertotal); – CEED Jun 16 '15 at 18:48
Try "select distinct username from vpss"; What are the results? – MiiinimalLogic Jun 16 '15 at 18:49
Result is the first Value in this case HunT3R – CEED Jun 16 '15 at 18:50
Something must be wrong in the way you're parsing the results. Are you looping through the result set to get the results? Because I know 100% that this is the correct answer. Post your code to parse the query results. – MiiinimalLogic Jun 16 '15 at 18:52
$querytotal = "SELECT DISTINCT(Username) FROM vpss"; $resulttotal = mysql_query($querytotal) or die(mysql_error()); foreach(mysql_fetch_array($resulttotal) as $usertotal); This is the query and im printing it with – CEED Jun 16 '15 at 18:53
I see, you're looking for an integer not the results to work with? Ok use: SELECT COUNT(DISTINCT(Username)) FROM vpss; Post me the number result. – MiiinimalLogic Jun 16 '15 at 18:57
Thats what i need! Thanks sir! the result is now 6 and correct. I wish i can vote up :D – CEED Jun 16 '15 at 19:00
I'm just going to edit the answer so it reflects the comments. – MiiinimalLogic Jun 16 '15 at 19:01

Devon Bessemer · Answer 2 · 2015-06-16T18:45:38.787

0

Use group by.

SELECT COUNT(*) FROM vpss GROUP BY `Username`

edited Jun 16 '15 at 18:45

answered Jun 16 '15 at 18:37

Devon Bessemer

34,461
9
69
95

@Devion ID's are not unique :/ – CEED Jun 16 '15 at 18:39
Give some sample data. – Devon Bessemer Jun 16 '15 at 18:40
http://gyazo.com/a3b47f044a9a865527e34b256292a02a There you go – CEED Jun 16 '15 at 18:44
@CEED, so you use username instead of ID? You couldn't figure that out on your own? – Devon Bessemer Jun 16 '15 at 18:46
the code is premade. My knowladge in PHP is really low. I need to count unique usernames in the Table and print them as number. – CEED Jun 16 '15 at 18:47
That is what this does... – Devon Bessemer Jun 16 '15 at 18:47

score 0 · Answer 3 · answered Jun 16 '15 at 18:40

0

Use distinct in the query SELECT COUNT(DISTINCT(ID)) FROM vpss

answered Jun 16 '15 at 18:40

Sarfaraz Khan

2,166
2
14
29

query SELECT COUNT only unique values

3 Answers3