multiple count and use subsctract function in one query I try to do more advance query in mysql but got error in query, i try to count and group by. here is my query:
&quot;select department,
(select count(id) from mcu group by dept) as tot1,
(select count(status_mcu) from mcu where status_mcu='done' group by dept) as tot2,
(tot1 - sum(select count(status_mcu) where status_mcu='done') as tot3,
FROM mcu&quot;

can you help me out ? what wrong with my query ? i do some search in other threads and this is what i got. try do query like :
&quot;select department,
select count(id),
sum(case when status_mcu='done' then 1 end)
...
&quot;

not worked too. Any help is appreciate.


my sql version is 5.5.25 – casthrotopes
Without seeing the exact error message returned, I can't be 100% certain, but there are two glaring errors:

Is the column name Department or Dept?  This does not seem clear.
You cannot reference tot1 unless it is first derived in a CTE or sub-select (derived table).
The subquery sum(select count(status_mcu) where status_mcu='done') does not have a FROM clause.  Furthermore, I'm not sure what purpose where aggregating a high-level aggregate is necessary/desired.
As a general critique, you're hitting the same table over and over again which is not optimal from a performance standpoint.

Start with this and see if it returns the desired results (if it doesn't, you'll need to clarify your question to provide the proper logic):
SELECT
  mcu.Dept
 ,COUNT(mcu.Id) AS Tot1
 ,SUM
    (
      CASE
        WHEN mcu.status_mcu='done' THEN 1
        ELSE 0
      END
    ) AS tot2
 ,COUNT(mcu.Id) - 
    SUM
      (
        CASE
          WHEN mcu.status_mcu='done' THEN 1
          ELSE 0
        END
      ) AS tot3
FROM
  mcu mcu
GROUP BY
  mcu.Dept