Hi,
I have an preorder table,
create table preorder (
order_id numeric(17) NOT NULL PRIMARY KEY,
purchase_date date_time NOT NULL,
.....)

data will be:
1, 1/23/2005
2, 12/30/2004
3, 12/21/2004
4, 2/12/2005
etc
Now I need to count the order for a user in every month so that it can't

Try

SELECT COUNT(*) FROM PreOrder
GROUP BY Year(purchase_date ),Month(purchase_date)
WHERE <condition>
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


[quoted text, click to view]

[quoted text, click to view]
Now I need to count the order for a user in every month
<<

if you need to count the order for a "user" then
the table structure you have posted is missing userid.
see following example

create table preorder (
order_id numeric(17) NOT NULL PRIMARY KEY,
purchase_date datetime NOT NULL,
userid int)

insert into preorder values (1, getdate(), 1)
insert into preorder values (2, getdate() -35, 1)
insert into preorder values (3, getdate() -60, 1)
insert into preorder values (4, getdate() -60, 1)
insert into preorder values (5, getdate() -35, 1)
insert into preorder values (6, getdate() -35, 1)
insert into preorder values (7, getdate(), 2)
insert into preorder values (8, getdate() , 2)
insert into preorder values (9, getdate() -60, 2)
insert into preorder values (10, getdate() -60, 3)
insert into preorder values (11, getdate() -35, 3)
insert into preorder values (12, getdate() -35, 3)
insert into preorder values (13, getdate() -160, 3)
insert into preorder values (14, getdate() -500, 3)
insert into preorder values (15, getdate() -600, 3)

-- query

select userid, datename(month,purchase_date) p_month, count(order_id) counts
from preorder
group by userid, datename(month,purchase_date)
order by userid

-- above query will not consider year part.
-- to consider year component you will have qurey as:

select userid, datename(month,purchase_date) p_month,
datename(year,purchase_date) p_year ,
count(order_id) counts
from preorder
group by userid, datename(month,purchase_date), datename(year,purchase_date)
order by userid

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com