A Question on Union !!

[sajith nath]

Dear All

I am new into Cognoise.com and have a quick query which i expect would get help from someone.

I am using Relational Database, and was trying to do a report based on Unions. When i ran the report i got the output like this.

Cm ID   Cmp Name   Sales    Cost   Amount 1   Amount 2   Quantity 1   Quantity 2
3456   Test Company   0   0   0   0   1   
3456   Test Company   0   0   400.   0   0   0
3456   Test Company   1   357.11   0   0   0   0

Sales and Cost was taken from Query 1, Amount 1 and Amount 2 is from Query 2, Quantity 1 and 2 from Query 3 ( and used the Union Function to get all data in one report). And if you see it, there are three rows coming in the report.

However i am expecting the Output to come in one Row like this.
Cm ID   Cmp Name   Sales    Cost   Amount 1   Amount 2   Quantity 1   Quantity 2
3456   Test Company   1   357.11   400.   0   1   

Could you please help me in how i can achieve that ?

NB : This is a sample Cmp ID taken among a 1000 odd row count.

Thanks in Advance.

Sajith

[iceacman]

you need to do join rather than union

[sajith nath]

Thanks for the Answer. So does that mean, it is not possible in Unions ?

[BigChris]

I think in both cases you just need to check your aggregation. If you sum the sales, cost, amount 1, amount 2, quantity 1 and quantity 2 fields it should do the job for you.

C

[navissar]

You can use a union for that. You create all the fields on all the queries by the same order, with null where the fields don't exist, an union them, then use aggregation. However, the correct and DB sound way to do it is with joins.

[Francis aka khayman]

just tried it. i got the a similar result. i changed the Aggregate Function property of my measures from Automatic to Total and I got total :)

[sajith nath]

Worked Perfectly for me !! ..Happy that my first post here itself was fruitful here. Thanks a ton All !!