Need help with writing a query

[hno]

hi,

I have the following information in my database :

id user_id p_id

2 2 38 2011-10-10 08:36:32 1010

5 4 38 2011-10-10 08:37:51 110

28 2 46 2011-10-13 17:05:27 244

16 2 38 2011-10-10 17:49:40 234244

18 4 38 2011-10-11 20:05:04 2010

19 4 38 2011-10-11 20:05:42 2510

 

I want to the number of rows with p_id: 38 for every user_id.For example I want the reslult for p_id:38 for user_id:2 to be 2 and p_id:38

for user 4 to be 3 .

How should I write the query to return that?So ,I want to have the umber of rows for every user_id for a specific p_id.

I hope you get what I mean.

 

Thanks

[requinix]

SELECT the user id, COUNT(1) AS count
FROM the table
GROUP BY the user id
WHERE the p_id is the value you want

[hno]

SELECT the user id, COUNT(1) AS count
FROM the table
GROUP BY the user id
WHERE the p_id is the value you want

 

Thanks a lot but what's wrong with this code :"SELECT  user_id, COUNT(1) AS count FROM bid_list GROUP BY  user_id  where bid_id = '40' "

it errors me "right syntax to use near 'where bid_id = '40' LIMIT 0, 30' at line 1"

 

[hno]

SELECT the user id, COUNT(1) AS count
FROM the table
GROUP BY the user id
WHERE the p_id is the value you want

 

Thanks a lot but what's wrong with this code :"SELECT  user_id, COUNT(1) AS count FROM bid_list GROUP BY  user_id  where bid_id = '40' "

it errors me "right syntax to use near 'where bid_id = '40' LIMIT 0, 30' at line 1"

 

Can anybody helps please?

[fenway]

WHERE comes before GROUP BY.

 

And use COUNT(*).

[requinix]

WHERE comes before GROUP BY.

 

And use COUNT(*).

Yeah. It was, uh, I totally intended to do that. So... yeah

I added the WHERE last - which was only supposed to mean chronologically.