Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
300 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

you can try like below , remove distinct

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

demo


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...