0

I'm attempting to join two queries with different where statements, on phpmyadmin mysql, The following is the code i need help with:

SELECT elec.`Property ID`, elec.`Year`, elec.`Month`, elec.`Electric Consumption`, gas.`Gas Consumption`
FROM
(
SELECT `Property ID`, `Year`, `Month`, `Year_ref`, Sum(`Electric Consumption`) AS 'Electric Consumption', Sum(`Electric Cost`) AS 'Electric Cost'
FROM utility_use
WHERE `Electric Cost` > 0
GROUP BY `Property ID`, `Year`, `Month`, `Year_ref`
HAVING `Property ID`= 4
ORDER BY `Year`, `Month`;
) as elec 
INNER JOIN
(
SELECT `Property ID`, `Year`, `Month`, `Year_ref`, `Gas Consumption` AS 'Gas Consumption', Sum(`Gas Cost`) AS 'Gas Cost'
FROM utility_use
WHERE `Gas Cost`> 0
GROUP BY `Property ID`, `Year`, `Month`, `Year_ref`
HAVING `Property ID`= 4
ORDER BY `Year`, `Month`;
) as gas
ON (elec.`Year` = gas.`Year`) AND (elec.`Month` = gas.`Month`) AND (elec.`Property ID` = gas.`Property ID`)
GROUP BY elec.`Property ID`, elec.`Year`, elec.`Month`, elec.`Electric Consumption`, gas.`Gas Consumption`
ORDER BY elec.`Year`, elec.`Month`;

It's giving me a syntax error, I can't seem to figure it out please help.

Carlos C. Estrella
  • 3
  • 1

2 Answers2

0

Get rid of your order by clauses in your subqueries.

Also, post what the syntax error says.

0
  • Not valid sql - Gas Consumption` is not contained in an aggregate function or the GROUP BY clause.
  • Correct SQL query,
Rajesh
  • 2,135
  • 1
  • 12
  • 14