PostgreSQL: reuse Column Data In Different Column Of The Same Query

Question

I'm trying to create a SELECT query that does several calculated fields on one of two tables. I'm new to SQL (I've looked at several free online tutorials, so I have a general idea), but I think my goal is a little out of my skill range.

I have two tables:

TreeRecord with columns ID (serial), Site (chr)

Each ID represents an individual tree.
TreeHistory with columns ID (serial), TreeID (int), DBH (int)

DBH is tree diameter.

Currently I can create this:

 |  Site  |  Total tree count of site  |  Avg DBH of site  |

I would like to have another column that can give the total count of trees over a particular size for each site. I can recreate this in a simple query, and my research on stack (SQL Select - Calculated Column if Value Exists in another Table) makes me feel that a nested SELECT is what I'm after but I can't get that to work. My current code is this:

SELECT 
    "TreeRecord"."Site", 
    count("TreeRecord".*) AS Total_Count, 
    round(avg("TreeHistory"."DBH"), 0) AS Average_DBH
FROM 
    "TreeRecord"
LEFT OUTER JOIN 
    "TreeHistory" ON "TreeRecord"."ID" = "TreeHistory"."TreeID"
GROUP BY 
    "Site"
ORDER BY 
    "Site" ASC;

Any help on this would be most appreciated.

Thank you

I'm not sure what your goal is, do you want to find all the trees of the site with WHICH size? the average? — gbalduzzi
– gbalduzzi, Commented May 23, 2016 at 15:42

Vamsi Prabhala · Accepted Answer · 2016-05-23 15:44:34Z

1

Use count with the specific size condition.

SELECT "TreeRecord"."Site",
count("TreeRecord".*) AS Total_Count, 
round(avg("TreeHistory"."DBH"),0) AS Average_DBH,
count(case when "TreeHistory"."DBH" > 10 then 1 end) as count_over_specific_size          
                                      ^^--change this size accordingly
FROM "TreeRecord"
LEFT OUTER JOIN "TreeHistory"
ON "TreeRecord"."ID" = "TreeHistory"."TreeID"
GROUP BY "Site"
ORDER BY "Site" ASC;

answered May 23, 2016 at 15:44

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gbalduzzi Over a year ago

wow i just finished my second university class about databases and never heard of that case statement, it is great!

Abelisto Over a year ago

@gbalduzzi Or count("TreeRecord".*) filter (where "TreeHistory"."DBH" > 10) for PostgreSQL 9.4 and above (as I remember).

Collectives™ on Stack Overflow

PostgreSQL: reuse Column Data In Different Column Of The Same Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related