Posted by Techhead on 02/08/07 20:42

On Feb 8, 1:51 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> You have to use COUNT for number of records, not SUM. You can use SUM to
> summarize a value if needed. Here is an example:
>
> CREATE TABLE #Test(mydate datetime, myvalue int)
>
> INSERT INTO #Test VALUES(DATEADD(hour, 2, getdate()), 2)
> INSERT INTO #Test VALUES(DATEADD(hour, 3, getdate()), 3)
> INSERT INTO #Test VALUES(DATEADD(hour, 4, getdate()), 4)
>
> SELECT COUNT(*) AS counts, SUM(myvalue) AS total
> FROM #Test
> WHERE mydate >= DATEDIFF(day, 0, getdate())
> AND mydate < DATEDIFF(day, 0, getdate() + 1)
>
> DROP TABLE #Test
>
> Regards,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thank you. COUNT was what I was looking for... sorry. Can I take this
one step further? I need to subtract the COUNT results of 1 query from
the COUNT results of another query.

Here are my 2 queries:

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '1'

SELECT COUNT (*) FROM TABLE.RECORDS WHERE DATEFIELD >= DATEDIFF(day,
0, getdate()) AND DATEFIELD < DATEDIFF(day, 0, getdate() + 1)AND
RECORD_TYPE = '2'

I need to subtract the results from query 2 from query 1

Once I get this, I am set.

[Back to original message]