column “ic.inscount” must appear in the GROUP BY clause or be used in an aggregate function

Question

When I run this query

 WITH install_count_join_date AS (
  SELECT 
    date_trunc('month', join_date) AS date, 
    COUNT(*) AS "inscountjoin"
  FROM 
    apps202_prod.search 
  WHERE 
    join_date >= '2016-06-01'
  AND
    app_id = 3
  GROUP BY 
    date_trunc('month', join_date)
), install_count AS (
  SELECT
    DATE(original_timestamp) AS date,
    COUNT(*) AS "inscount"
  FROM 
    apps202_prod.search 
  WHERE 
    original_timestamp >= '2016-06-01'
  AND
    app_id = 3
  GROUP BY 
    DATE(original_timestamp)
) 

SELECT 
  date_trunc('month', mr.date) AS "money_revenue_date",
  SUM(mr.amount) AS "amt",
  ic.inscount AS "install_count"
FROM 
  mysql_apps202_prod.apps202_prod_money_revenue mr
  join install_count ic on date_trunc('month', ic.date) = date_trunc('month', mr.date)
WHERE
  date_trunc('month', mr.date)  >= '2016-06-01'
AND
  mr.app_id = 3

GROUP BY
  date_trunc('month', mr.date)

I get this error:

column ic.inscount must appear in the GROUP BY clause or be used in an aggregate function

That error is pretty self explanatory... Since your final query is using an aggregate (sum), you need to add the other fields to a group by clause... — sgeddes
I don't need to add the inscount to the group by since I already aggregated it in the with clause as you see it in the above code — HashTag007
You are summing the amount column in your outer query. So yes you do have to use group by again (for both fields)... — sgeddes
when I put the inscount in the groupby clause it gives me many records, the expected result is only 4 records since the where clause in the main query is date_trunc('month', mr.date) >= '2016-06-01' — HashTag007
At this point, table structure, sample data and expected results would be helpful. Your first common table expression isn't being used and I'm not completely sure what you're trying to do. You might be better off deleting this post and re-posting your actual question (vs the error that I've already commented on). — sgeddes

Susang Susang · Accepted Answer · 2016-09-22T03:35:09

You have to change your select statement as below, as you got so many feedback above about the issue you are dealing with, if you are using group by then you select all the columns with aggregate function except in group by. In your case ic.inscount neither in group by nor using any aggregate function so please use one of below according to your requirement.

SELECT 
   date_trunc('month', mr.date) AS "money_revenue_date",
   SUM(mr.amount) AS "amt",
   ic.inscount AS "install_count"
FROM mysql_apps202_prod.apps202_prod_money_revenue mr
join install_count_join_date ic on date_trunc('month', ic.date) = date_trunc('month', mr.date)
WHERE date_trunc('month', mr.date)  >= '2016-06-01'
    AND mr.app_id = 3
GROUP BY date_trunc('month', mr.date), ic.inscount

OR

SELECT 
    date_trunc('month', mr.date) AS "money_revenue_date",
    SUM(mr.amount) AS "amt",
    MAX(ic.inscount) AS "install_count" --Any Addregate Function
FROM mysql_apps202_prod.apps202_prod_money_revenue mr
join install_count_join_date ic on date_trunc('month', ic.date) = date_trunc('month', mr.date)
WHERE date_trunc('month', mr.date)  >= '2016-06-01'
    AND mr.app_id = 3
GROUP BY date_trunc('month', mr.date)

column “ic.inscount” must appear in the GROUP BY clause or be used in an aggregate function

1 Answers