CODEWITHSUNDEEP
mysql
Shawn
Shawn

Reputation: 911

MySQL Need return a count of unique combinations of cols

I'm coding a video game and using MySQL to display an icon showing the player where treasure is located. The database stores the locations by their X and Y on a square map. I would like to also count the number of items on each square.

So given a table such as this

Id   x   y
==  ==  ==
1    2   3
2    3   2
3    3   2
4    4   4
5    4   4
6    4   4

I would like to return something to the effect of 

x y count
= = =====
4 4 3
3 2 2
2 3 1

Upvotes: 6

Views: 6337

Answers (3)

Jerem
Jerem

Reputation: 470

What about concatenate your 2 columns and use DISTINCT ?

Select x, y, count(DISTINCT CONCAT(x, '_', y)) as 'count' from mytable group by x, y

Upvotes: 6

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Use GROUP BY clause for results. 

Select x, y, count(*) as 'count' from mytable group by x, y

Upvotes: 14

Keith Randall
Keith Randall

Reputation: 23265

SELECT x,y,SUM(1) FROM table GROUP BY x,y;

Upvotes: 0

Related Questions