How do I get aggregate results with join in PostgreSQL

Question

I have two tables named Purchase and PurchaseOrder and each of the tables have the following fields:

Purchase
---------
id
date_shipped
id_po          -> FK of Purchase Order

PurchaseOrder
---------
id
date_po

I need to group them based on PurchaseOrder's date_po year. So far, I've been using the date_shipped field of the Purchase table with the following query:

select extract(year from date_shipped), count(*) from purchase
    group by(extract(year from date_shipped));

Now, I need to join the two tables and group them by the year of PurchaseOrder's date_po instead of Purchase's date_shipped field. I've tried various queries but can't quite achieve the results. One of the queries I tried is:

select extract(year from po.date_shipped), count(*) from purchase p 
    inner join purchase_order po on p.id_po = po.id
    group by(extract(year from po.date_po));

Any help would be appreciated!

Please be more specific how it doesn't work, because the query as shown will result in an error because your purchase_order table has no column date_shipped. And the resulting obvious error message should be included in the question, and if that isn't the actual problem, then you need to revise your question to include the actual query and explain why it doesn't work (include sample data, expected result and actual result and why you think that is wrong). — Mark Rotteveel
– Mark Rotteveel, Commented Mar 7, 2018 at 18:05
@MarkRotteveel yep, that was supposed to be date_po and not date_shipped. Updated the question. — Kevin
– Kevin, Commented Mar 7, 2018 at 18:08
Try ( select Year(po.date_po), count(*) from purchase p inner join purchase_order po on p.id_po = po.id group by Year(po.date_po); ) — Raj
– Raj, Commented Mar 7, 2018 at 18:09
See the second part of my comment, you need to explain how the query doesn't work. — Mark Rotteveel
– Mark Rotteveel, Commented Mar 7, 2018 at 18:09
@RajneeshVaishwar PostgreSQL has no function YEAR, extract(year from <timestamp/date>) is the correct function (which is defined in the SQL standard) — Mark Rotteveel
– Mark Rotteveel, Commented Mar 7, 2018 at 18:12

Mureinik · Accepted Answer · 2018-03-07 18:07:31Z

1

If you have a group by clause you can only use terms from there (or row-function applied to them) and aggregate terms in the select list:

SELECT     EXTRACT(YEAR FROM po.date_po), COUNT(*)
FROM       purchase p 
INNER JOIN purchase_order po ON p.id_po = po.id
GROUP BY   EXTRACT(YEAR FROM po.date_po)

answered Mar 7, 2018 at 18:07

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

Kevin Over a year ago

I had been using the wrong column, it was supposed to be id_purchase_order and not id_po. Thank's a lot for your time. Accepting answer to stop wasting anyone else's time.

Collectives™ on Stack Overflow

How do I get aggregate results with join in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related