mysql query - Need a little help with grouping...

Question

I'm wondering how to write this query, it's a little complicated...

This is an example of what's being stored in the table:

KID     Utype Qtype Qname     Resp UID
Q24-YYY Case T001 HM-99678 N8944 2455
Q24-YYY Case T001 HM-99678 N8944 9874
Q24-YYY Case F099 HM-99678 N8944 6554
Q24-YYY Case F099 HM-99678 SD789 2331
Q24-YYY Case F099 HM-99678 SD789 1772
Q24-YYY Case T001 HM-99678 PX667 8897
Q24-YYY Case F099 HM-99678 PX667 4412
Q24-YYY Case F099 HM-99678 SD789 6643
Q24-YYY Case F099 HM-99678 PX667 3221
Q24-YYY Case T001 HM-99678 SD789 6337

What I'm trying to do is to first, group it by the Resp column, then by the Qtype column, and add a count column for the Resps... So that, the result looks like this:

KID     Utype Qtype Qname     Resp COUNT
Q24-YYY Case T001 HM-99678 N8944 2
Q24-YYY Case F099 HM-99678 N8944 1
Q24-YYY Case T001 HM-99678 SD789 1
Q24-YYY Case F099 HM-99678 SD789 3
Q24-YYY Case T001 HM-99678 PX667 1
Q24-YYY Case F099 HM-99678 PX667 2

I've been playing with various iterations using group by, but so far, I get invalid group column errors and such...

Answer 1

You should group all columns that you're not counting:

select KID, Utype, Qtype, Qname, Resp, count(*)
from YourTable
group by KID, Utype, Qtype, Qname, Resp

MySQL doesn't really enforce that, but if you forget to group on a column you use, you essentially get a random value for it.

Answer 2

First of all, you can't, in a single query group by one column, and then by another column. You establish a group by over a set. The columns in that set have no order to them, If yo want an order established that will have to be established by an Order By Clause.

Second, anything you want to be generated in the output of the query that is not a column or expression which is part of that group By Set must be based on an aggregate function...

So making sime assumptions, (that KID, UType, amd Qname are always determined by Resp and QType)

  Select KID, UType, Resp, Qtype, Qname, Count(*) RespCount
  From Table
  Group By KID, UType, Resp, Qname, Qtype
  Order By Resp, QType

Answer 3

According to standard there should work this query. ( I know that MySQL has a few things non-standardly in implementation of group by clause so it isn't 100% )

Select distinct Table.*, tmp.Count From
  ( Select Qtype, Resp, count(*) AS Count From table Group By Resp, Qtype ) tmp
  Join Table ON Table.Qtype = tmp.Qtype and Table.Resp = tmp.Resp