2

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nodebase
  • 2,510
  • 6
  • 31
  • 46

2 Answers2

6

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 *.

Per documentation:

USING is 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 of JOIN USING has 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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Ok I just realized that. Thank you. How do I take the average of the 'epatmpg' ? So the query would just return the value? – nodebase Dec 06 '14 at 00:22
  • You mean *one* avg over the whole (joined) table? – Erwin Brandstetter Dec 06 '14 at 00:24
  • Yes. But I think I just figured it out. How would YOU do it? – nodebase Dec 06 '14 at 00:28
  • 1
    Erwin's answer is excellent. Just one caveat - the above may not get you what you want for average if there are "duplicate" rows in the query, it all depends on the specifics of the data. We'd have to know what is in the vehv2pub table and dayv2pub table to be sure. – EGP Dec 06 '14 at 00:49
  • 1
    @EGP: Exactly. I provided the avg over the whole derived table like requested. I added some explanation. – Erwin Brandstetter Dec 06 '14 at 01:06
0

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
EGP
  • 616
  • 5
  • 11
  • I should note, that is the average per houseid. If you want the average across all houses, just remove the group by and the houseid from the select. In either case, depending on what data is in the tables, it may not wind up with the exact result you want if there are "duplicates." You might have to do a subquery if that's the case. – EGP Dec 06 '14 at 00:27