Looking for help coding this SQL statement

Table1

has
ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE

sample data

ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE
1, 1234, 4/1/2005,
2, 1234, 4/1/2005, X
3, 1234, 4/1/2005,
4, 1111, 7/1/2005,
5, 1111, 7/1/2005, X

What I want to do is get a count of Unique items per day.

However, if there is a SUB_CODE (X) then that should be subtracted.

So the final table should be

ID_DESCRIPTION, SERVICE_DATE
1234, 4/1/2005,


Thanks!

ct witter schrieb:
> Looking for help coding this SQL statement
>
> Table1
>
> has
> ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE
>
> sample data
>
> ID, ID_DESCRIPTION, SERVICE_DATE, SUB_CODE
> 1, 1234, 4/1/2005,
> 2, 1234, 4/1/2005, X
> 3, 1234, 4/1/2005,
> 4, 1111, 7/1/2005,
> 5, 1111, 7/1/2005, X
>
> What I want to do is get a count of Unique items per day.
>
> However, if there is a SUB_CODE (X) then that should be subtracted.
>
> So the final table should be
>
> ID_DESCRIPTION, SERVICE_DATE
> 1234, 4/1/2005,
>
>
> Thanks!
>

SELECT *
FROM (SELECT Service_Date,
COUNT(Id_Description) -
2 * COUNT(Decode(Sub_Code, 'X', 'X', NULL)) Cnt
FROM Table1 t
GROUP BY Service_Date)
WHERE Cnt != 0

If sub_code may contain only 'X' and NULL, you can leave out decode
expression.

Best regards

Maxim

Worked Great --- Thanks!!