CODEWITHSUNDEEP
sqlsql-servergroup-bysubquery
user3272370
user3272370

Reputation: 47

SQL Group By statement within subquery

I am trying to write a query that will return the number of "proactive call cases" made by each employee per month as well as the total number of all cases. Essentially, I am looking to return 3 columns; an EmployeeName, a count of the Proactive Calls for that month, and a count of the total calls that month. To try and accomplish this I used a subquery. Here is what I have so far:

Select OpenedByName AS EmployeeName, Count(OpenedByName) As NumberOfProactiveCallsMONTH,
  (Select Count(OpenedByName)
   From table1
   Where OpenDate Between '2/1/2014' and '3/1/2014' Group By OpenedByName) AS
   TotalTicketsMONTH
From table1
Where OpenDate Between '2/1/2014' and '3/1/2014' AND ProblemType = 17 
Group By OpenedByName

After running the query I got the error saying that:

"Msg 512, Level 16, State 1, Procedure SalesMTDSubQuery, Line 7 Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."

I think it returned this error due to the GroupBy statement that I added in the subquery. When I remove the GroupBy it will just return the same value for every employee. I'm fairly new to using SQL so I'm not really sure of a good way around this. Any help would be much appreciated.

Thanks!

Upvotes: 1

Views: 5557

Answers (4)

GarethD
GarethD

Reputation: 69819

You need to put a relation from the inner query to the outer query:

SELECT  t1.OpenedByName AS EmployeeName, 
        COUNT(t1.OpenedByName) As NumberOfProactiveCallsMONTH,
        (   SELECT  COUNT(OpenedByName)
            FROM    table1 t2
            WHERE   t2.OpenDate BETWEEN '2/1/2014' AND '3/1/2014'
            AND     t1.OpenedByName = t2.OpenedByName
        ) AS TotalTicketsMONTH
FROM    table1 t1
WHERE   t1.OpenDate BETWEEN '2/1/2014' AND '3/1/2014' 
AND     t1.ProblemType = 17 
GROUP BY t1.OpenedByName;

However you can simplify the query by using a conditional aggregate (i.e. only counting when additional criteria is met):

SELECT  t1.OpenedByName AS EmployeeName,
        COUNT(CASE WHEN t1.ProblemType = 17 THEN t1.OpenedByName END) As NumberOfProactiveCallsMONTH,
        COUNT(t1.OpenedByName) As TotalTicketsMONTH
FROM    table1 t1
WHERE   t1.OpenDate BETWEEN '2/1/2014' AND '3/1/2014' 
GROUP BY t1.OpenedByName;

The next improvement would be to change your date format to one that not culture specific - is 2/1/2014 2nd January or 1st February? If you use yyyyMMdd there is no ambiguity.

Finally, your query will include tickets opened on 1st March at midnight, I suspect these should not be included in February's data, you would be better of using >= and `<'. Making your final query:

SELECT  t1.OpenedByName AS EmployeeName,
        COUNT(CASE WHEN t1.ProblemType = 17 THEN t1.OpenedByName END) As NumberOfProactiveCallsMONTH,
        COUNT(t1.OpenedByName) As TotalTicketsMONTH
FROM    table1 t1
WHERE   t1.OpenDate >= '20140201' 
AND     t1.OpenDate < '2014031' 
GROUP BY t1.OpenedByName;

For further reading on Date range queries:

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

You need to relate the sub-select to the outer statement, otherwise, as you noticed, you return multiple lines in the sub-select which doesn't work.

SELECT  OpenedByName AS EmployeeName
      , COUNT(OpenedByName) AS NumberOfProactiveCallsMONTH
      , ( SELECT    COUNT(OpenedByName)
          FROM      table1 b
          WHERE     OpenDate BETWEEN '2/1/2014' AND '3/1/2014'
                AND b.OpenedByName = a.OpenedByName
          GROUP BY  OpenedByName
        ) AS TotalTicketsMONTH
FROM    table1 a
WHERE   OpenDate BETWEEN '2/1/2014' AND '3/1/2014'
        AND ProblemType = 17
GROUP BY OpenedByName

But you can greatly simplify this by using a conditional sum instead of a sub-select:

SELECT  OpenedByName AS EmployeeName
      , SUM(ProblemType = 17) NumberOfProactiveCallsMONTH
      , COUNT(OpenedByName) AS TotalTicketsMONTH
FROM    table1
WHERE   OpenDate BETWEEN '2/1/2014' AND '3/1/2014'
GROUP BY OpenedByName

Upvotes: 0

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

You can use like this

Select a.OpenedByName AS EmployeeName, Count(a.OpenedByName) As NumberOfProactiveCallsMONTH,
b.TotalTicketsMONTH  AS   TotalTicketsMONTH
From table1 a
INNER JOIN   
  (Select OpenedByName ,Count(OpenedByName) AS TotalTicketsMONTH   From table1
  Where OpenDate Between '2/1/2014' and '3/1/2014' Group By OpenedByName) b ON
  a.OpenedByName =  b.OpenedByName
Where a.OpenDate Between '2/1/2014' and '3/1/2014' AND a.ProblemType = 17 
Group By a.OpenedByName

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166606

How about something like

SELECT  OpenedByName AS EmployeeName,
        SUM(CASE WHEN ProblemType = 17 THEN 1 ELSE 0 END) AS NumberOfProactiveCallsMONTH,
        COUNT(1) TotalTicketsMONTH
FROM Table1
Where OpenDate Between '2/1/2014' and '3/1/2014' 
GROUP BY OpenedByName

The error you received was due to the fact that when using a sub query as a column, it cannot return more than 1 value.

Now the way you had it structured, it would have returned all the COUNT values per OpenedByName, instead of a single value per OpenedBy.

If you realy wish to use your quer, you need to limit the lookup in the column select to the actual OpenedByName. So something like

Select OpenedByName AS EmployeeName, Count(OpenedByName) As NumberOfProactiveCallsMONTH,
  (Select Count(OpenedByName)
   From table1 t
   Where OpenDate Between '2/1/2014' and '3/1/2014' 
   AND t.OpenedByName = t1.OpenedByName) AS
   TotalTicketsMONTH
From table1 t1
Where OpenDate Between '2/1/2014' and '3/1/2014' AND ProblemType = 17 
Group By OpenedByName

Upvotes: 1

Related Questions