Jump to content


Photo

Counting rows based on a column value?


  • Please log in to reply
2 replies to this topic

#1 rockonxox

rockonxox
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 01 December 2005 - 12:09 AM

The site I am using this for is a secret santa database for a web group I'm a part of. Everything works fine, but I'm noticing that many members have more "gift items" than others.

The table for gifts that have been sent (which I am wanting to count), records many presents for each individual, which is why I need to count the rows based on the column of "hfname". So in short, I want to count the rows, using the column hfname, and group them based on if the name shows up in 0, 1-3, 5-10, or 10+ rows.

I want to set up the gift analysis like so:
Members with no gifts
(insert php to count hfname values returning 0 rows)

Members with 1-3 gifts
(insert php to count hfname values returning 1-3 rows)

Members with 5-10 gifts
(insert php to count hfname values returning 5-10 rows)

Members with over 10 gifts
(insert php to count hfname values returning >10 rows)

---------------

This is really the first site I've done using databases, so it's more a learning tool than anything of real use, at least not unless it's the holidays =)

#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 01 December 2005 - 12:29 AM

Not sure whether this is what you want
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(*) FROM yourtable WHERE hfname='0' [!--sql2--][/div][!--sql3--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(*) FROM yourtable WHERE hfname BETWEEN '1' AND '3' [!--sql2--][/div][!--sql3--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] COUNT(*) FROM yourtable WHERE hfname>10 [!--sql2--][/div][!--sql3--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 rockonxox

rockonxox
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 01 December 2005 - 01:25 AM

Not sure if that will work. Hfname is just a column fieldname... I want to be able to get results like this:

Member has 0 presents
Kelly
Alia
Candymara

Member has 1-3 presents
Tigerlily
Dana
KirstinVP

Members has 5-10 presents
JillianVP

Member has 10+ presents
None


So I'd need to count rows based on how many times a value occurs in the hfname column...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users