Luca Luca - 3 months ago 7
MySQL Question

MySQL Query from one table - Select same field twice

First of all, I'm a total newbie to MySQL and I fully apologize if I'll ask something which has already been answered under different "terms". I have been searching for a solution to this for over a week now, and I was unable to come up with one, probably because I am not using the right keywords. Please bear with me and my ignorance in this sense :)

I have a table with the following fields:

ID int(11) NO PRI auto_increment
Building int(11) YES MUL
CounterNo int(11) YES MUL
ReadingDate datetime YES
StartMeter int(11) YES
EndMeter int(11) YES
CostKw decimal(10,6) YES
Multiplier smallint(3) YES


I want to make a query, which I would use to populate a comparison line chart, that will return the following:

Building | Cons1 | Cons2 | Month
1 1year ybefore Month


Where the month name would then be used to populate the X axis, and Cons1 would be the consumption for each building for each month in the last year, while Cons2 would be the consumption for each building for each month in the year before.

I have already been playing around, and so far I've gotten to the following one:

SELECT Building,
CASE
WHEN ReadingDate >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
THEN (EndMeter - StartMeter)
ELSE NULL
END
AS 'Cons1',
CASE
WHEN ReadingDate BETWEEN DATE_SUB(NOW(), INTERVAL 2 Year) AND DATE_SUB(NOW(), INTERVAL 1 Year)
THEN (EndMeter - StartMeter)
ELSE NULL
END
AS 'Cons2',
CONCAT(
MONTHNAME (
tblElectricReadings.ReadingDate
)
) AS MonthRecorded
FROM tblElectricReadings
WHERE ReadingDate >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
ORDER BY ReadingDate


This produces the results I want, but it doesn't "consolidate" (and again, sorry if I am using the wrong words here) Cons1 and Cons2 together, but rather puts a null value to either fields, creating double the records I need. Basically, the query works fine, but it's not the result I want. I would like both Cons1 and Cons2 to be populated with relevant consumption according to each month, so that I can populate the line graph (with 2 lines, 1 for Cons1 and the other for Cons2) accordingly.

I hope I have been clear enough, if not please forgive me and tell me if you need further info.

Thanks in advance for any help you may be able to provide.

EDIT: To answer Paul - There's only 1 entry for each month for each building, so it's not a problem. But thanks for noticing that! Also thanks a million to xQbert - your answer is exactly what I was looking for :) Thanks for your time man.

Answer

Since we know that each record will have a null value using a max (or min) eliminates the null record values and combines the results into one row/record.

SELECT  Building,
        max(CASE 
            WHEN ReadingDate >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
            THEN (EndMeter - StartMeter)
            ELSE NULL
        END)
    AS 'Cons1',
        max(CASE 
            WHEN ReadingDate BETWEEN DATE_SUB(NOW(), INTERVAL 2 Year) AND DATE_SUB(NOW(), INTERVAL 1 Year) 
            THEN (EndMeter - StartMeter)
            ELSE NULL
        END)
    AS 'Cons2',
CONCAT(
        MONTHNAME (
            tblElectricReadings.ReadingDate
        )
    ) AS MonthRecorded
FROM    tblElectricReadings
WHERE   ReadingDate >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
GROUP BY Building, CONCAT(MONTHNAME(tblElectricReadings.ReadingDate))
ORDER BY ReadingDate