asked on

SQL Group by

Hello,
can you please help.
I'm trying to group this properly to get only 1 line per client.
but I get quite few.
here is the code

Select Distinct 'Accountnumber' AS [Accountnumber],
'WEB' AS [WEB],
'CSR' AS [CSR]
From FinalizedOrders
WHERE 1 = 1
Union All
Select CONVERT(varchar, Accountnumber, 1) AS [Accountnumber],
(Select CONVERT (varchar, Count (Operator), 1) where Operator like 'web' ) AS [WEB] ,
(Select CONVERT (varchar, Count (Operator), 1) where Operator not like 'web' ) AS [CSR]
From FinalizedOrders
where accountnumber IN (423,116) and orderdate between '2013-01-01' and '2013-12-31'
Group by Accountnumber, Operator

Sample Results
Accountnumber      WEB      CSR
116        NULL       31
116        NULL      6
116      NULL      30
423        NULL      13
423        NULL      15
116        6679      NULL
423        NULL      13
423        474        NULL

it should be
Accountnumber      WEB      CSR
116        6679      67
423        474        31

thanks

ASKER CERTIFIED SOLUTION

chaau

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

hello,
thanks for your help,
i still get more than one line,

sample
Accountnumber      WEB      CSR
423        0        15
116        6679      0
423       0        13
423       474        0
116       0        50
116       0        48
423       0        6
423        0        2
116        0        31

W.E.B

ASKER

OK, it's good,
I just needed to change
Group by Accountnumber,) AS T

thanks

W.E.B

ASKER

thank you