asked on

SQL JOIN

Good Day,
can someone help me with a joint statement.
with below union, I'm getting multiple results for same Accountnumber.
I need to see only one line.
your help is appreciated.

Select Accountnumber, (Select Name from Clients where Clients.Accountnumber = ActiveOrders.Accountnumber) AS [Client],
(Select LastInvoiceDate from Clients where Clients.Accountnumber = ActiveOrders.Accountnumber) AS [LastInvoiceDate],
sum(SubTotalAmount) AS [SubTotalAmount]
From ActiveOrders Where primarysalespersonuserid = 208
Group By Accountnumber
UNION ALL
Select Accountnumber, (Select Name from Clients where Clients.Accountnumber = FinalizedOrders.Accountnumber) AS [Client],
(Select LastInvoiceDate from Clients where Clients.Accountnumber = FinalizedOrders.Accountnumber) AS [LastInvoiceDate],
sum(SubTotalAmount) AS [SubTotalAmount]
From FinalizedOrders Where primarysalespersonuserid = 208
Group By Accountnumber

OMC2000

'union all' returns all records from both record sets, you could replace it with 'union', it removes duplicated rows.

But if records differ it returns both

Jim Horn

In addition to the above UNION vs. UNION ALL answer, UNION only eliminates duplicates when EVERY column value matches, so if when you say 'I need to see only one line' for AccountNumber, if there are multiple rows for a given AccountNumber and other values are different then you'll have to spell out for us how you want the other columns to behave: Min, Max, Sum, just pick the first one, etc.

W.E.B

ASKER

Hello,
even with a Union, I still get multiple returns.
Example
Accountnumber      Client        LastInvoiceDate        SubTotalAmount
8283        LE CUI 2018-05-25 00:00:00.000      145.99
8283        LE CUI        2018-05-25 00:00:00.000      239.67

I'm trying to get
Accountnumber      Client        LastInvoiceDate        SubTotalAmount
8283        LE CUI 2018-05-25 00:00:00.000      385.66

Thank you

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you Eric.