CODEWITHSUNDEEP
sqlpostgresql
cpburke94
cpburke94

Reputation: 1277

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

Upvotes: 1

Views: 41

Answers (1)

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Your answer below

SELECT  state, count(nct_id) count_trials
FROM facilities
where country  = 'United States'  group by state, country order by count(nct_id) desc

Test

select   state , count(nct_id) count_trials from (            
select 'NCT04832854' nct_id  ,  'Illinois' state,    'United States'country union all
select 'NCT04832851' ,  'Michigan',    'United States' union all
select 'NCT04832802' ,  'California',  'United States' union all
select 'NCT04832813' ,  'Naples',      'Italy' union all
select 'NCT04832813' ,  'Illinois',    'United States'
) a where country  = 'United States'  group by state, country order by count(nct_id) desc

Upvotes: 1

Related Questions