SQL - Trying to COUNT records

[argie]

Hey all,

I'm using SQL Query Anaylzer and Enterprise Manager.

I have a database with a name, ID, quantity, type, and date column.

What I'm trying to do, and having problems trying to figure out how to do it is tally up records by grouping them by only their month and year within the date column. The day is irrelevant for this query.

Can anyone lend me some assistances on how to code this?


Thanks and respectfully,
RG

[stuka]

I'd guess that your WHERE clause would need to do a date range for the current month (WHERE date < month_end AND date > month_begin), and count that. You'd probably have to do that for each relevant month/year combo.

[argie]

I'm still a bit of a novice when it comes to this, but this is what i used:

select *
from "TABLENAME"
WHERE DATE < 4/31/03
AND DATE > 4/1/03

I get the error msg:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'DATE'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'DATE'.


If you could further assist that would be very appreciated. Thank you!


-rg-

[argie]

AWESOME IT WORKS!

Thanks!

[argie]

ok.. i attached a visual diagram of what i'm actually trying to do... although the COUNT method you described me did answer one of my questions... now i'm stuck with this predicament of actually tallying rows.

For TABLE1 and TABLE2, they're are multiple transactions made by ID1 but on different WEEKs... so one ID1 could appear several times but within the same month/year. What i'm trying to do is tally each ID1 and the amount of times they made a transaction in a each month/year which the results will be inserted into TABLE3. The differences for TABLE1 and TABLE2 are the TYPES have been divided up there. So each table has its own TYPE and TABLE3 will have both.

Anyone's help is much appreciated.

Thank you,
RG





Originally posted by stuka
I'd guess that your WHERE clause would need to do a date range for the current month (WHERE date < month_end AND date > month_begin), and count that. You'd probably have to do that for each relevant month/year combo.

[argie]

the following is the code i have thus far in relation to the previous post:

SELECT *
FROM TABLE1
WHERE WEEK > '2003-10-01'
AND WEEK < '2003-10-31'
ORDER BY ID1 ASC