Create a count of Column A to distribute by Column B

I want to get a count of the number of trials by state that took place in the United States. The information I need comes from the nct_id, state and country columns. I am in pgAdmin on a Macbook Pro. A small sample is below:

nct_id        state       country  NCT04832854   Illinois    United States  NCT04832851   Michigan    United States  NCT04832802   California  United States  NCT04832813   Naples      Italy  NCT04832813   Illinois    United States  

I want to group the nct_id's by state, but only if the country is 'United States'. I have this so far. I get an error saying ERROR: syntax error at or near "AS" LINE 3: COUNT(nct_id) AS Count_Trials. I'm not sure what the syntax error is here though.

SELECT nct_id, state  FROM facilities  COUNT(nct_id) AS Count_Trials  GROUP BY state  HAVING Country = 'United States'  

I'd like the output to be like the below:

state      Count_Trials  Illinois   2  Michigan   1  California 1  

https://stackoverflow.com/questions/67394682/create-a-count-of-column-a-to-distribute-by-column-b May 05, 2021 at 11:07AM