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 |
|
sqlawesome
Starting Member
2 Posts |
 Posted - 2011-12-11 : 17:26:30
|
| Hey, I'm trying to write an SQL query that groups memberids and then counts them to output the number of times they appear. I was wondering if anyone could offer advice on how I achieve this.. this is my code so far. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-11 : 17:55:41
|
If you want to get the MEMBERIDs and how many times they occur in the output of your current query, you can do this:SELECT MEMBER.MEMBERID, COUNT(*)FROM RENTAL INNER JOIN RENTALLINE ON (RENTALLINE.RENTALID = RENTAL.RENTALID) INNER JOIN FLIX_MEMBER ON (MEMBER.MEMBERID = RENTAL.MEMBERID)GROUP BY MEMBERIDORDER BY MEMBERID; If that is not exactly what you are looking for, can you post sample output that you want to see? |
 |
|
|
sqlawesome
Starting Member
2 Posts |
Posted - 2011-12-11 : 18:28:28
|
| Thats brilliant thanks, it works, how would I AVG the number of occurances of the memberid??Thankyou |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-11 : 23:47:43
|
Hope you asked for this!SELECT SUM(MemCnt)*1.0/NULLIF(COUNT(DISTINCT MEMBERID),0) AS MemOccurAvgFROM(SELECT MEMBER.MEMBERID, COUNT(*) AS MemCnt FROM RENTAL INNER JOIN RENTALLINE ON (RENTALLINE.RENTALID = RENTAL.RENTALID) INNER JOIN FLIX_MEMBER ON (MEMBER.MEMBERID = RENTAL.MEMBERID)GROUP BY MEMBERID)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Group by and count