Postgres DISTINCT to single row

Question

how to DISTINCT to single row

here is my code:

SELECT 
wagon.name w_name,
'' as su_name,
'' as m_name,
'' as sk_name,
'' as p_name,
'' as b_name,
'' as o_name,
total w_total,
0 as su_total,
0 as m_total,
0 as sk_total,
0 as p_total,
0 as b_total,
0 as o_total

FROM wagoning 
LEFT JOIN wagon ON wagoning.wagon_id = wagon.id 

UNION ALL

SELECT 

'' w_name,
name su_name,
'' m_name,
'' sk_name,
'' p_name,
'' b_name,
'' o_name,
0 w_total,
total su_total,
0 m_total,
0 sk_total,
0 p_total,
0 b_total,
0 o_total

FROM drying 
LEFT JOIN drier ON drying.drier_id = drier.id 

UNION ALL

SELECT 

'' w_name,
'' su_name,
quantity::varchar as m_name,
'' sk_name,
'' p_name,
'' b_name,
'' o_name,
0 w_total,
0 su_total,
total m_total,
0 sk_total,
0 p_total,
0 b_total,
0 o_total

FROM
bagging

UNION ALL

SELECT 

'' w_name,
'' su_name,
'' m_name,
quantity::varchar as sk_name,
'' p_name,
'' b_name,
'' o_name,
0 w_total,
0 su_total,
0 m_total,
total sk_total,
0 p_total,
0 b_total,
0 o_total


FROM
storing

UNION ALL

SELECT 

'' w_name,
'' su_name,
'' m_name,
'' sk_name,
quantity::varchar as p_name,
'' b_name,
'' o_name,
0 w_total,
0 su_total,
0 m_total,
0 sk_total,
total p_total,
0 b_total,
0 o_total

FROM
processing

UNION ALL

SELECT 

'' w_name,
'' su_name,
'' m_name,
'' sk_name,
'' p_name,
quantity::varchar as b_name,
'' o_name,
0 w_total,
0 su_total,
0 m_total,
0 sk_total,
0 p_total,
quantity * net b_total,
0 o_total

FROM
boxing

UNION ALL

SELECT 

'' w_name,
'' su_name,
'' m_name,
'' sk_name,
'' p_name,
'' b_name,
'' o_name,
0 w_total,
0 su_total,
0 m_total,
0 sk_total,
0 p_total,
0 b_total,
part o_total

FROM
boxing

Please check the screenshot below: top result (above the red) how it is working now. What I want is below the red line as in single row

screenshot

Please don't add images. Please add your ouput and expected output to the question as text. — S-Man
– S-Man, Commented Dec 12, 2018 at 14:45

Kaushik Nayak · Accepted Answer · 2018-12-12 14:42:41Z

1

I suppose you want MAX over the query result of all UNION ALL

select MAX(w_name) as w_name,
       MAX(su_name)as su_name,
      -- ..  other columns

FROM
(
SELECT 
wagon.name w_name,
'' as su_name,
'' as m_name,
'' as sk_name,
'' as p_name,
'' as b_name,
'' as o_name,
total w_total,
0 as su_total,
0 as m_total,
0 as sk_total,
0 as p_total,
0 as b_total,
0 as o_total

FROM wagoning 
LEFT JOIN wagon ON wagoning.wagon_id = wagon.id 

UNION ALL

SELECT 
..

answered Dec 12, 2018 at 14:42

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

4 Comments

Marat Raim Over a year ago

Can't use max. It should be repeater in Ireport

Kaushik Nayak Over a year ago

@MaratRaimberdievich : But, I don't see that in your query, it has always only one non-null column per query

Marat Raim Over a year ago

I tried but it didnt work on repeater. it shows only one row always. I need to repeat those tables as one row, and repeat for other records. Well a client wants those shitty reports :(

Marat Raim Over a year ago

Well jasper iReport does the repeating part. I just give a row it will repeat for other records.

Collectives™ on Stack Overflow

Postgres DISTINCT to single row

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related