CODEWITHSUNDEEP
sql
Zi0n1
Zi0n1

Reputation: 594

Simple way to use the SUM function in SQL

So I am having some issues with creating a SPROC for a report for a new application that reads calculations from devices. These readings are all just basic numbers and are taken every week. Currently I am trying to combine the calculations for each month.

I started toying around with the SUM function since I havent done sub-queries within a a SUM function before and I have the below:

SELECT
    (SELECT SUM(Reading) FROM Readings WHERE (MONTH(Readings.ReadingDate) = 1) AND MeterID = 1) AS January1,
    (SELECT SUM(Reading) FROM Readings WHERE (MONTH(Readings.ReadingDate) = 1) AND MeterID = 2) AS January2,
    (SELECT SUM(Reading) FROM Readings WHERE (MONTH(Readings.ReadingDate) = 1) AND MeterID = 3) AS January3     
FROM
    Readings
WHERE
Year(Readings.ReadingDate) = 2013

My tables and fields that will be getting info pulled from are:

Meters: MeterID, MeterName

Readings: ID, userID, MeterID, Reading, ReadingDate

I'm aware that it is currently not in Stored Procedure format right now, basically I have a feeling that I am getting in over my head with the current format because there is 19 meters and then if I take that with the 12 months of the year I am going to have one monster length of a SPROC for something I feel like could be simpler. Any help would be AWESOME.

Thanks!

Upvotes: 0

Views: 126

Answers (1)

James Cronen
James Cronen

Reputation: 5763

Use "GROUP BY":

SELECT
    MONTH(Readings.ReadingDate), MeterID, SUM(Reading) FROM Readings
WHERE 
    YEAR(Readings.ReadingDate) = 2013
GROUP BY 
    MONTH(Readings.ReadingDate), MeterID;

This should give you 19 * 12 rows, one for each combination of reading month and meter.

(Didn't test this, but syntax should be close.)

Upvotes: 2

Related Questions