Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Group by

Posted on 2014-01-02
4
Medium Priority
?
262 Views
Last Modified: 2014-01-02
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
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 1200 total points
ID: 39752824
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
 

Author Comment

by:W.E.B
ID: 39752829
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
 

Author Comment

by:W.E.B
ID: 39752850
OK, it's good,
I just needed to change
Group by Accountnumber,) AS T

thanks
0
 

Author Closing Comment

by:W.E.B
ID: 39752851
thank you
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question