Solved

SQL Group by

Posted on 2014-01-02
4
246 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:Wass_QA
  • 3
4 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 300 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:Wass_QA
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:Wass_QA
ID: 39752850
OK, it's good,
I just needed to change
Group by Accountnumber,) AS T

thanks
0
 

Author Closing Comment

by:Wass_QA
ID: 39752851
thank you
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now