confusing count() clause in SQL query

I need some help with a query (sorry, I'm not too good at this!)

I have a table like:

productid    userid     DateTracked
```````````````````````````````````
1             1          2010-09-11
2             1          2010-09-11
3             1          2010-09-11
4             1          2010-09-11
1             2
2             2
1             1          2010-09-13
2             1          2010-09-13

so, I want to count how many times each user has bought each product. Actually, I want to select the number of users who has bought from the website more than once. this isn't a product-userid stuff at all, I am just explaining the scenario as best as I can.

here, Userid 1 is associated with pid 1,2,3,4 and then again, on a different date, 1,2.

I tried

select count(*) from MyTable group by ProductID

and it gives me a list of numbers that I am not sure what they signify or if thats what I want, is the query correct?开发者_StackOverflow

edited to simplify: There are dates, and all I want to select is the number of times, the user id is repeated on different dates. In the above table, userid 1 has two counts - one for date 11, and one for 13. and I have x number of userid's.

I think that explains my problem more accurately. sorry. so how do I select count of all userid's that have different dates?

many thanks

The code

select count(*) from MyTable group by ProductID

Returns just the count of individual product id's giving no information as to which product they relate to.

select ProductID, count(*) as cnt from MyTable group by ProductID

gives you the productID and its corresponding count

To get the number of products bought by a user

select UserID, count(*) as cnt from MyTable group by UserID

And to get the number of times a user has bought a specific product

Select UserID, ProductID, Count(*) as cnt from MyTable group by UserID, ProductID

Count user purchases

Include the commented out line to only get users that have made more than 1 purchase.

with Pur as
(
    select UserID, DateTracked
    from Purchase
    group by UserID, DateTracked
)
select UserID, count(DateTracked) as NumberOfPurchases
from Pur
group by UserID
-- having count(DateTracked) > 1

This can be further simplified as well:

select UserID, count(distinct DateTracked)
from Purchase
group by UserID;

I haven't checked their execution plans, but the second one is probably faster. The second one only counts distinct purchase dates per user which is your main objective.

Count product purchases

If a user can buy multiple same products per purchase this is the query you should use:

select ProductID, count(*)
from Purchase
group by ProductID;

If you just want to count the number of purchases when a certain product was included in the purchase than this is the query you should use:

select ProductID, count(distinct DateTracked)
from Purchase
group by ProductID;

The group by function is used in conjunction with your aggregate function, Count(*), to perform the aggregate function over the set of data that fits in your group. You are counting the number of times each productId occurs in your table. See http://www.w3schools.com/sql/sql_groupby.asp

If you want to know what each count means you should include the ProductId

SELECT ProductId, Count(*)
FROM MyTable
GROUP BY ProductId

If you are only interested in counts of a certain size or products of a certain type you can limit it further with the WHERE and HAVING clauses.

SELECT ProductId, Count(*)
FROM MyTable
WHERE ProductType = 'MyType'
GROUP BY ProductId
HAVING Count(*) > 3

Edit: For your updated question of grouping by date and userId you want the following.

SELECT UserId, Date, COUNT(*)
FROM MyTable
GROUP BY UserId, Date

Note: You will need to convert your Date column if it stored as DATETIME to the DATE type for the group by clause to give you the results you expect!

You can use the statement
SELECT productid, userid, count(*) FROM MyTable GROUP BY productid, userid

You are computing counts grouped by the ProductID, but you aren't indicating which count belongs to which ProductID. Just include the ProductID as an output column:

select ProductID, count(*) from MyTable group by ProductID

Based on your question, however, you don't actually want to count by product. If you want to count the number of distinct dates on which the user bought stuff, then group by userid and count distinct dates:

select UserID, COUNT(DISTINCT DateTracked) from MyTable group by UserID

if you to just see how every user by you can use this: SELECT userid,COUNT(productid) FROM tbl GROUP BY userid I think this will give you: users ids and how many items they buy... you can write: SELECT userid,COUNT(productid) AS cc FROM tbl GROUP BY userid HAVING cc > 1 then you'll got only the ids and items counts of users who have bought more than once...