Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpk79
Starting Member
4 Posts |
 Posted - 2006-08-31 : 20:49:12
|
| I have a table that stores usernames and product names, among other things, but these are the two columns I am working on creating a query for. In this table, in some cases a username may have millions of rows with the same product name and may have rows for other differnt product names as well. I want to see how many unique product names a username has, so I do not need to know if there are millions of the same product names this username has, I want the millions to be refined into 1. So this is the query that I have:SELECT DISTINCT productnm, usernameFROM mainOrder By usernameI am rather new to SQL and I wanted to run this by you all to see if I am going about this the right way. Will this truly give me what I am looking for? Again, I am looking to see all product names a username has, and I want to strip out the duplicates so if they for example have three million rows of the same product name, I only want to see that they do have a product and it shows in my query results have having 1.Thanks for any assistance. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-08-31 : 22:19:31
|
| That would do it, but you'll need another query to get the counts:SELECT username, Count(productnm)FROM (SELECT DISTINCT productnm, username FROM main)GROUP BY usernameHTH,Tim |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-01 : 01:37:28
|
| select username, count(distinct productnm)from maingroup by usernamePeter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Is this the most efficient query for this?