I have a derived table that results in this format:
THENAME | THESTATUS | THECOST
----------------------------------
name1 | statusX | cost1.1
name1 | statusX | cost1.2
name2 | statusZ | cost2.1
name2 | statusZ | cost2.1
name3 | statusY | cost3.1
(...)
nameN | status(X,Y,Z) | cost
each nameN will always have the same status but cost will be different. I am trying to come up with a result that looks something like this
Status Groups | Count of Names | Total Cost of Group
X 2 10
Y 5 20
Z 4 5
basically, display the total cost of each status and the number of items each status contains.
Going for something like
SELECT
thestatus,
COUNT(DISTINCT thestatus),
SUM(thecost)
FROM THETABLE
GROUP BY thestatus
doesn't work and I am thinking if it is even possible to group simultaneously two columns..
EDIT - https://www.db-fiddle.com/f/bB1RSy8GV35FGEi3Lr1Tqw/0
EDIT - Not sure if it's the version of MySQL or what, but in the sample data I think it works properly but not in the actual query. It would be impossible to re-create the entire schema and the whole query but perhaps it is something else...
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…