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:

"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"

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 :

"select department,
select count(id),
sum(case when status_mcu='done' then 1 end)

not worked too. Any help is appreciate.

my sql version is 5.5.25 – casthrotopes

  • Hi, and welcome to the forum! What version of MySQL are you using - should be in every question! Also, please provide a fiddle with your tables and data and also your desired result. Jan 21 '21 at 7:39
  • thanks for fast response @Vérace, my sql version is 5.5.25 Jan 21 '21 at 7:45
  • My first question might be "why are you running an antique?" - but I know that programmers/DBAs don't always have control over their environments - now, what about your tables and data and desired resutl? Jan 21 '21 at 7:46
  • here is my query It is logically wrong (the subquery will most likely return a rowset, not a scalar value). And it is synthactically wrong (nested aggregate functions, lost data source). Post the task itself, not only the attempt to solve it.
    – Akina
    Jan 21 '21 at 7:50
  • When responding to a request for more information about a question, please put that information into the text of the question itself - you can let the person who requested it know by putting a comment below theirs and putting @ + their handle into the body of your response - e.g. for me it would be @Vérace. Jan 21 '21 at 10:19

Without seeing the exact error message returned, I can't be 100% certain, but there are two glaring errors:

  1. Is the column name Department or Dept? This does not seem clear.
  2. You cannot reference tot1 unless it is first derived in a CTE or sub-select (derived table).
  3. 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.
  4. 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):

 ,COUNT(mcu.Id) AS Tot1
        WHEN mcu.status_mcu='done' THEN 1
        ELSE 0
    ) AS tot2
 ,COUNT(mcu.Id) - 
          WHEN mcu.status_mcu='done' THEN 1
          ELSE 0
      ) AS tot3
  mcu mcu

  • Thankyou your query work like magic! it work now. Jan 22 '21 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.