CODEWITHSUNDEEP
sqlsql-servert-sql
thehiddencheese
thehiddencheese

Reputation: 93

SQL sub query not being grouped

I am having some trouble with this sub query, I am trying to count the amount of columns called id where sStatus='Open' but grouped with the sCompany. As of now its just returning a count of all id's where sStatus is open for the entire table.

Maybe a sub query isn't the best way to handle this and I may need a join? I have tried not using a sub query and just using a count, however the WHERE clause for sStatus='Closed' would just make it return 0.

SELECT Sum(nQuotetotal) AS nsales,
       sCompany,
       Max(dtFirstClosed) AS dtMostRecent,
       (SELECT Count(id)
              FROM   customer_quotes
              WHERE  sStatus='Open') AS nOpenQuotes
FROM   customer_quotes
WHERE  sQuoteType='cam'
AND    bDeleted=0
AND    sStatus='Closed'
AND    dtFirstClosed BETWEEN " + getDate + ' GROUP BY sCompany ORDER BY nSales DESC

Upvotes: 0

Views: 224

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use conditional aggregation instead of a subquery. I'm not sure what filter conditions you want applied, but something like this:

SELECT SUM(CASE WHEN sStatus = 'Closed' AND
                     dtFirstClosed > ?
                THEN nQuotetotal ELSE 0
           END) AS nsales,
       sCompany,
       MAX(CASE WHEN sStatus = 'Closed' AND
                     dtFirstClosed > ?
                THEN dtFirstClosed
           END) AS dtMostRecent,
       SUM(CASE WHEN sStatus = 'Open' THEN 1 ELSE 0 END) as  nOpenQuotes
FROM customer_quotes
WHERE sQuoteType = 'cam' AND
      bDeleted = 0
ORDER BY nSales DESC;

Note that the filter conditions in the WHERE clause apply to both open and closed quotes.

Upvotes: 1

Charlieface
Charlieface

Reputation: 71233

You can use a conditional window aggregate for this. It needs to go in a derived table because the outer WHERE would otherwise exclude those rows.

It's unclear which of the WHERE predicates you want to affect the count, you may want to move some of them to the inner query.

DO NOT inject data into your query, use proper parameterization

SELECT SUM(nQuotetotal) AS nsales,
       sCompany,
       MAX(dtFirstClosed) AS dtMostRecent,
       nOpenQuotes
FROM   (
    SELECT *,
        COUNT(CASE WHEN sStatus = 'Open' THEN 1 END)
         OVER (PARTITION BY sCompany) AS nOpenQuotes
    FROM customer_quotes
) cq
WHERE  sQuoteType = 'cam'
AND    bDeleted = 0
AND    sStatus = 'Closed'
AND    dtFirstClosed BETWEEN @dateFrom AND @dateTo
GROUP BY sCompany, nOpenQuotes
ORDER BY nSales DESC;

Upvotes: 0

Related Questions