Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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
0
W.E.B
Asked:
W.E.B
  • 3
1 Solution
 
chaauCommented:
Modify the second select statement like this:
Select  CONVERT(varchar, Accountnumber, 1)  AS [Accountnumber],
CONVERT (varchar, Count (CASE WHEN Operator like 'web' THEN Operator ELSE NULL END)) AS [WEB] ,
CONVERT (varchar, Count (CASE WHEN Operator like 'web' THEN NULL ELSE Operator END))  AS [CSR]
From FinalizedOrders
where accountnumber IN (423,116) and orderdate between '2013-01-01' and '2013-12-31'
Group by Accountnumber, Operator

Open in new window

BTW, for the first select statement you do not need to do SELECT FROM, just use:
Select  'Accountnumber' AS [Accountnumber],
 'WEB' AS [WEB],
 'CSR' AS [CSR]

Open in new window

So, the whole query will be:
Select  'Accountnumber' AS [Accountnumber],
 'WEB' AS [WEB],
 'CSR' AS [CSR]
UNION ALL
SELECT [Accountnumber], [WEB], [CSR] FROM
(Select  CONVERT(varchar, Accountnumber, 1)  AS [Accountnumber],
CONVERT (varchar, Count (CASE WHEN Operator like 'web' THEN Operator ELSE NULL END)) AS [WEB] ,
CONVERT (varchar, Count (CASE WHEN Operator like 'web' THEN NULL ELSE Operator END))  AS [CSR]
From FinalizedOrders
where accountnumber IN (423,116) and orderdate between '2013-01-01' and '2013-12-31'
Group by Accountnumber, Operator) AS T

Open in new window

0
 
W.E.BAuthor Commented:
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
0
 
W.E.BAuthor Commented:
OK, it's good,
I just needed to change
Group by Accountnumber,) AS T

thanks
0
 
W.E.BAuthor Commented:
thank you
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now