How do I change the query to just show one row SQL?-CodePudding

I am trying to compile a view of how many instances occur in each month. I have the below code, which gets me the answer, but also gives multiple rows. How do I limit the view to just one row, with each column being the amount of instances each month?

CREATE VIEW new_users_per_month AS 
SELECT ( 
                SELECT  COUNT(id) AS January
                FROM            master_table 
                WHERE           month(created_at)=1) AS January,                
                ( 
                SELECT  COUNT(id) AS February
                FROM            master_table 
                WHERE           month(created_at)=2) AS February,
                ( 
                SELECT  COUNT(id) AS March
                FROM            master_table 
                WHERE           month(created_at)=3) AS March
FROM master_table;

CodePudding user response：

Do conditional aggregation :

select count(case when month(created_at) = 1 then id end) as Jan,
       count(case when month(created_at) = 2 then id end) as Feb,
       count(case when month(created_at) = 3 then id end) as Mar
from master_table t;

CodePudding user response：

Not properly tested, i will create dummy and test in a moment, but in support of my comment

select *
from
(
    select 
    t.id,
    month(t.created_at) as MTH_CRE
    from master_table as t
) as q
pivot
(
    count(id) for MTH_CRE in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as pvt