In Postgresql, if you have a table that has two columns: a list of purchases, and another column that is the state in which those purchases were made, how would you count the number of purchases by state.
1 Answer
if your column names are state and purchases you can group by the state column and use count(purchases) to count all the instances of purchases within that state. I have posted an example below. You will just need to fill in the table name that you are pulling from.
SELECT
state,
count(purchases) as purchase_count
FROM
[table_name]
GROUP BY
state
Secondarily, you can order the with the most purchases to the least by using ORDER BY and referencing the column number. Example below:
SELECT
state,
count(purchases) as purchase_count
FROM
[table_name]
GROUP BY
state
ORDER BY
2 DESC
