Prev: Deleting Duplicate records in SQL Server
Next: When does a query/subquery return a NULL and when no value atall, not even a NULL?
From: Kirk on 28 Apr 2010 10:39
Hello,

I have tried numerous solutions to what should be a simple issue, but
I have had no success. I am trying to gather some simple math values
from a single table, and I cannot create the proper select statement.

The below query works and provides the correct values:

SELECT TOP (100) PERCENT MAX(DistCode) AS DistCode,
MAX(DistName) AS DistName,
MAX(CustServRep) AS CustServRep,
COUNT(DistCode) AS AllOrders
FROM dbo.[TABLE OrderHistory] AS OH
WHERE (M2K_Timestamp >= CONVERT(DATETIME, '2010-03-01 00:00:00',
102))
AND (M2K_Timestamp < CONVERT(DATETIME, '2010-04-01 00:00:00', 102))
GROUP BY DistCode
ORDER BY DistName

WITHIN this set of data there are some records that contain 'EDI' in a
field called 'OrderType'. I want to count the number of records that
match this criteria and total it the way I do the "DistCode" above.

This is an example of something I have tried that does NOT work:

SELECT TOP (100) PERCENT MAX(DistCode) AS DistCode,
MAX(DistName) AS DistName,
MAX(CustServRep) AS CustServRep,
COUNT(DistCode) AS AllOrders,
CASE WHEN OrderType = 'EDI' THEN COUNT(OrderType) ELSE 0 END AS
OrderTypeCount
FROM dbo.[TABLE OrderHistory] AS OH
WHERE (M2K_Timestamp >= CONVERT(DATETIME, '2010-03-01 00:00:00',
102))
AND (M2K_Timestamp < CONVERT(DATETIME, '2010-04-01 00:00:00', 102))
GROUP BY DistCode
ORDER BY DistName

As you might expect, this is the error that is returned:

Column 'dbo.TABLE OrderHistory.OrderType' is invalid in the select
list because it is not contained in either an aggregate function or
the GROUP BY clause.

I have tried using derived tables and CTE's and I always run into the
same issue: I either get only the records that match ALL of the
criteria (removing the records that do NOT contain 'EDI') or it
removes all of the records completely.

I am sure I am missing something simple, and I would greatly
appreciate any suggestions.
Thank you!

From: Plamen Ratchev on 28 Apr 2010 10:56
Try this:

SELECT MAX(DistCode) AS DistCode,
MAX(DistName) AS DistName,
MAX(CustServRep) AS CustServRep,
COUNT(DistCode) AS AllOrders,
COUNT(CASE WHEN OrderType = 'EDI' THEN 1 END) AS OrderTypeCount
FROM dbo.[TABLE OrderHistory] AS OH
WHERE M2K_Timestamp >= '20100301'
AND M2K_Timestamp < '20100401'
GROUP BY DistCode
ORDER BY DistName;

There is no need for TOP(100) PERCENT, it is redundant.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Kirk on 28 Apr 2010 11:16
On Apr 28, 10:56 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Try this:
>
> SELECT MAX(DistCode) AS DistCode,
>         MAX(DistName) AS DistName,
>         MAX(CustServRep) AS CustServRep,
>         COUNT(DistCode) AS AllOrders,
>         COUNT(CASE WHEN OrderType = 'EDI' THEN 1 END) AS OrderTypeCount
> FROM dbo.[TABLE OrderHistory] AS OH
> WHERE M2K_Timestamp >= '20100301'
>    AND M2K_Timestamp <  '20100401'
> GROUP BY DistCode
> ORDER BY DistName;
>
> There is no need for TOP(100) PERCENT, it is redundant.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

That worked GREAT! Now that I see what you are doing, it looks so
eloquently simple (& I fell like an idiot for not thinking of that
myself).

THANK YOU very much for your quick & helpful reply!
 | 
Pages: 1
Prev: Deleting Duplicate records in SQL Server
Next: When does a query/subquery return a NULL and when no value atall, not even a NULL?