CODEWITHSUNDEEP
mysql
Gilbert Hair
Gilbert Hair

Reputation: 35

Sum mysql results into specified group

    SELECT 
    f.hmis, sa.number_of_computers, sa.service_area
FROM
    facility_service_area sa,
    facility f
WHERE
    f.hmis = sa.facility_hmis


hmis   comp serv_area
80703   3   MCH
80706   1   LD
80701   2   ART
80701   3   ART
80701   2   LD
80701   3   LD
80701   2   MCH
80701   1   MCH
80702   4   MCH
80707   1   MCH
80707   4   MCH

I would like to sum all computers where in each specified service area under each hmis where results look like this.

hmis   comp. Service_area
80703   3   MCH
80706   1   LD
80701   5   ART
80701   5   LD
80701   3   MCH
80702   4   MCH
80707   4   MCH

Upvotes: 0

Views: 38

Answers (2)

Chandana Kumara
Chandana Kumara

Reputation: 2645

Use GROUP BY with SUM() aggregate function. 
SELECT f.hmis,
       SUM(sa.number_of_computers),
       sa.service_area
FROM facility_service_area sa,
     facility f
WHERE f.hmis = sa.facility_hmis
GROUP BY sa.service_area


Also you can use INNER JOIN for this.

SELECT f.hmis, SUM(sa.number_of_computers), sa.service_area
FROM facility_service_area sa INNER JOIN
facility f
ON f.hmis = sa.facility_hmis
GROUP BY sa.service_area

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

First. Never use comma in the FROM clause. Always use proper, explicit JOIN syntax.

You just want a GROUP BY:

SELECT f.hmis, sa.service_area, SUM(sa.number_of_computers)
FROM facility_service_area sa JOIN
    facility f
    ON f.hmis = sa.facility_hmis
GROUP BY f.hmis, sa.service_area;

In an aggregation query, I almost always put the aggregation columns first followed by the computed columns. This helps prevent errors, because it is easy to check that the columns used for the aggregation match what is being returned.

Upvotes: 1

Related Questions