Query unique objects of a column
Date: April 5th 2016
Last updated: April 5th 2016
A simple check to see all distinct items in a table.
DISTINCT
USE surfdiary;
SELECT DISTINCT(boardname) AS name FROM surferprofile_board;
+---------------------+
| name |
+---------------------+
| Ducks Nuts |
| Ducks Nuts (Thick) |
| Fader |
| Puzzle Piece |
| Whiplash Taj Burrow |
| Enough Said |
| Face Dancer |
| V2 Shortboard |
| Rare Bird |
| Short Round |
| V2 Grinder |
| Rooster |
| Double Shot |
| Double Shot (Thick) |
| NULL |
+---------------------+
COUNT distinct items using GROUP BY
SELECT boardname, COUNT(*) AS count FROM surferprofile_board GROUP BY boardname;
+---------------------+-------+
| boardname | count |
+---------------------+-------+
| NULL | 1 |
| Double Shot | 8 |
| Double Shot (Thick) | 4 |
| Ducks Nuts | 9 |
| Ducks Nuts (Thick) | 4 |
| Enough Said | 6 |
| Face Dancer | 10 |
| Fader | 5 |
| Puzzle Piece | 6 |
| Rare Bird | 15 |
| Rooster | 7 |
| Short Round | 7 |
| V2 Grinder | 6 |
| V2 Shortboard | 8 |
| Whiplash Taj Burrow | 5 |
+---------------------+-------+
SELECT boardperformance, COUNT(*) AS count FROM surferprofile_board GROUP BY boardperformance;
+------------------+-------+
| boardperformance | count |
+------------------+-------+
| NULL | 1 |
| AR | 13 |
| FH | 45 |
| HP | 23 |
| SW | 19 |
+------------------+-------+
Adding more columns
SELECT boardname, boardperformance, COUNT(*) AS count FROM surferprofile_board GROUP BY boardname;
+---------------------+------------------+-------+
| boardname | boardperformance | count |
+---------------------+------------------+-------+
| NULL | NULL | 1 |
| Double Shot | SW | 8 |
| Double Shot (Thick) | SW | 4 |
| Ducks Nuts | HP | 9 |
| Ducks Nuts (Thick) | HP | 4 |
| Enough Said | FH | 6 |
| Face Dancer | FH | 10 |
| Fader | HP | 5 |
| Puzzle Piece | FH | 6 |
| Rare Bird | FH | 15 |
| Rooster | AR | 7 |
| Short Round | SW | 7 |
| V2 Grinder | AR | 6 |
| V2 Shortboard | FH | 8 |
| Whiplash Taj Burrow | HP | 5 |
+---------------------+------------------+-------+