0

I have two tables as follows (in a Postgres 10 database):

Maize:

id|acreage|date      |region
1 |20     |2018-10-18|A
2 |10     |2018-10-07|A
3 |26     |2018-10-18|B
4 |06     |2018-10-07|B

Paddy:

id|acreage|date      |region
1 |17     |2018-10-18|A
2 |29     |2018-10-09|A
3 |24     |2018-10-18|B
4 |09     |2018-10-09|B

I want to get the following result:

Maize|Paddy|date      |Region
20   |17   |2018-10-18|A
Null |29   |2018-10-09|A
10   |Null |2018-10-07|A

Do note that when the dates do not match, I would like to get a Null in the cell.

I've been trying to write a query like this, but I only get the first row, and not the 2nd & 3rd Rows:

Select c1.acreage as "Maize", c2.acreage as "Paddy", c1.date, c1.region FROM 
Maize c1 
FULL OUTER JOIN Paddy c2 ON c1.date =c2.date AND c1.region=c2.region 
order by c1.date;

Obviously the date in the Join is causing the issue; But I don't know how to remove it, and still get the Matching values in one row.

  • I am not able to find the criteria how to join / how to aggregate rows in your question. The sort order in the SELECT query disagrees with the sample result. - as does the whole result. Please clarify the question. – Erwin Brandstetter Oct 30 '18 at 12:14
1
SELECT c1.acreage Maize, 
       c2.acreage Paddy, 
       COALESCE(c1."date",c2."date") "date", 
       COALESCE(c1.region, c2.region) region 
FROM Maize c1 
FULL OUTER JOIN Paddy c2 
    ON c1."date"=c2."date" AND c1.region=c2.region 
ORDER BY "date";

fiddle

  • ORDER BY c1."date" is probably not practical in this query. Maybe ORDER BY 3 ... Or use the USING keyword in the join condition to simplify everything ... – Erwin Brandstetter Oct 31 '18 at 14:25
  • @ErwinBrandstetter You're right, thank you. It was a result of auto-replace... and my inattention... table alias removed. While using ORDER BY "date" it points to output field name. – Akina Oct 31 '18 at 16:20
  • Right. Or use the USING clause to simplify. And rather don't skip the AS keyword for column aliases. Like: SELECT c1.acreage AS "Maize", c2.acreage AS "Paddy", date, region FROM Maize c1 FULL JOIN Paddy c2 USING (date, region) ORDER BY date; – Erwin Brandstetter Oct 31 '18 at 16:27
0

Here is an alternate solution:

select 
SUM(CASE WHEN type = 'm' THEN acreage ELSE 0 END) as "Maize",
SUM(CASE WHEN type = 'p' THEN acreage ELSE 0 END) as "Paddy",
date,
region
from
(
    select 'm' as "type", m.acreage, m.date, m.region from maize m
    UNION ALL
    select 'p' as "type", p.acreage, p.date, p.region from paddy p
)
group by date, region;

I ended up using this, because this SQL query is generated by code, and the number of tables can vary. This kind of query is more readable, and more easily writeable by code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.