0

I have a SQL table with three columns: name, date of birth, and current state. I want to return a result-set with the number of times each state appeared in the original table. The result-set will have 2 columns: state and count.

I'm guessing I will need to use GROUP BY or COUNT but I haven't been able to put it together.

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
itstoocold
  • 2,385
  • 2
  • 12
  • 15
  • 1
    Possible duplicate of [How to use count and group by at the same select statement](http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) – Sᴀᴍ Onᴇᴌᴀ Mar 13 '17 at 03:39
  • A nice brief explanation is [How does GROUP BY work?](http://stackoverflow.com/questions/7434657/how-does-group-by-work) – dkherbert Mar 13 '17 at 03:50

2 Answers2

2

The following syntax will work for most SQL engines:

SELECT expression1, expression2, ... expression_n,
  COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, ... expression＿n
Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement.
aggregate＿expression
This is the column or expression whose non-null values will be counted.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. These are conditions that must be met for the records to be selected.
ORDER BY expression
Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated.
ASC
Optional. ASC sorts the result set in ascending order by expression. This is the default behavior, if no modifier is provider.
DESC
Optional. DESC sorts the result set in descending order by expression.
 1

Refer to documentation for the specific engine used:

...etc

For the "question"

I have a SQL table with three columns: name, date of birth, and current state. I want to return a result-set with the number of times each state appeared in the original table. The result-set will have 2 columns: state and count.

such a query could be constructed like this:

SELECT current_state as state, count(*) as count
FROM data 
GROUP BY current_state

See an example of this in this SQLfiddle.

1https://www.techonthenet.com/sql/count.php

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
  • Tip of today: Switch to modern, explicit `JOIN` syntax! – jarlh Mar 13 '17 at 08:01
  • @jarlh good idea - instead of citing the MySQL documentation (who knows why that documentation would have an example with out any _ON_ conditions for the JOIN?? :/ ...), it now includes a citation from a non-database Engine specific source and includes links to some of the most prominantly used r-DBMS engines – Sᴀᴍ Onᴇᴌᴀ Mar 13 '17 at 16:49
1

You can do it like this...

SELECT current_state, count(current_state) FROM table GROUP BY current_state
imprezzeb
  • 706
  • 1
  • 7
  • 18