How to prevent MYSQL Group by errors if no rows are returned?-CodePudding

I have a simple query

SELECT id, sum(credits_in) AS total_credits, count(id) as total_rows 
            FROM transactions  WHERE id = '$id'  GROUP BY $id

It works perfectly fine if there are rows (transactions by the target user), but on some queries where the target is a new user and has no transactions yet (no rows), this error comes up.

  Error: Can't group on 'sum(credits_in)'"

For now, I have a work around where i have an initial query to simply check and count the rows, before running the main query w/ sum() groupings... But I'm interested in a way where I wouldn't need to issue a row-count SQL line. I wish the main SQL would just return sum=zero if no rows were found instead of raising an error.

CodePudding user response：

As you already know the ID, and there is only one involved, remove ID from the select clause and remove the group by clause. Then supply at least one row in a union to avoid the no row problem.

SELECT
       sum(credits_in) AS total_credits
     , count(*)-1 as total_rows 
FROM (
      SELECT 0.00 as credits _in
  union all
      SELECT credits_in
      FROM transactions
      WHERE id = '$id'
      ) as d