I have this query but I'm getting two columns of houseid:
How do I only get one?
SELECT vehv2pub.houseid, vehv2pub.vehid, vehv2pub.epatmpg,
dayv2pub.houseid, dayv2pub.trpmiles
FROM vehv2pub, dayv2pub
WHERE vehv2pub.vehid >= 1
AND dayv2pub.trpmiles < 15
AND dayv2pub.houseid = vehv2pub.houseid;
And also, how do I get the average of the epatmpg? So the query would just return the value?
The most elegant way would be to use the USING clause in an explicit join condition:
SELECT houseid, v.vehid, v.epatmpg, d.houseid, d.trpmiles
FROM vehv2pub v
JOIN dayv2pub d USING (houseid)
WHERE v.vehid >= 1
AND d.trpmiles < 15;This way, the column houseid is in the result only once, even if you use SELECT *.
USINGis a shorthand notation: it takes a comma-separated list of column names, which the joined tables must have in common, and forms a join condition specifying equality of each of these pairs of columns. Furthermore, the output ofJOIN USINGhas one column for each of the equated pairs of input columns, followed by the remaining columns from each table.
To get the average epatmpg for the selected rows:
SELECT avg(v.epatmpg) AS avg_epatmpg
FROM vehv2pub v
JOIN dayv2pub d USING (houseid)
WHERE v.vehid >= 1
AND d.trpmiles < 15;
If there are multiple matches in dayv2pub, the derived table can hold multiple instances of each row in vehv2pub after the join. avg() is based on the derived table.
not 100% sure this works in postgres sql, but something like this gets the average in SQL server:
SELECT vehv2pub.houseid, avg(vehv2pub.epatmpg)
FROM vehv2pub, dayv2pub
WHERE vehv2pub.vehid >= 1
AND dayv2pub.trpmiles < 15
AND dayv2pub.houseid = vehv2pub.houseid
GROUP BY vehv2pub.houseid
houseidare from different tables and you get both because you select both.