mysql - SQL Count occurrences in a column and calculate total of another

Question

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

Answer 1

you can try like below , remove distinct

SELECT
     THESTATUS, 
     COUNT(THENAME),
     SUM(THECOST)
FROM THETABLE
GROUP BY thestatus

demo