Topic awaiting preservation: mySQL: trying to count rows where bit_count in SET > 0

I'm not sure if there are any mySQL guru's out there, but I'm struggling to sort out a query to count how many rows have some or all of the items in a SET field.

I have a table with a SET column type that contains 15 items.

I've tried COUNT (IF(bit_count(items)>0)) etc, but I didn't get that to work. So perhaps SUM might be what I needed to use....

At first I thought the following would do the trick...

SELECT

SUM(IF (BIT_COUNT(items)=15,1,0)) AS all,

SUM(IF (BIT_COUNT(items)>0 and BIT_COUNT(items)<15, 1,0)) AS some

FROM mytable

But that just returns a 1 if any rows have all items, bit_count(items)=15 and/or 1 if any rows have some items, bit_count(items)>0.

possibly 'WHERE BIT_COUNT != null'. I believe when a set field has none of it's set values selected the database registers it as null.

I've been labouring at this and have come up with a solution, not exactly what I wanted but it works. It requires a loop to process the data, which I still think I can avoid if my mySQL query knowledge was good enough.


BTW, I've used BIN purely to make it easier for me to handle the CASE statements.

"SELECT

LPAD(BIN(items+0),15,'0') AS num_items,

COUNT(items) count

FROM mytable

group by items"

	

$all=0;

$some=0;

while ($r = mysql_fetch_array($res))

{

    extract($r);

    switch ($num_items)

    {

        case "111111111111111":

	   $all+=$count;

	   break;

        case "000000000000000":

           break;

        case NULL:

           break;

	default:

	   $some+=$count;

    }

}

you know...I just had a thought...why not just tell it to select all records that have at least one of the options selected and then, instead of using that loop to count them count like this:

$count = mysql_num_rows($result);

Skaarj, in SQL, the correct syntax for determining null values in the database is "colname IS NULL" or "colname IS NOT NULL". I would make sure I know this stuff before I try helping people.

First off, why are you doing so much work? If someone did what you're doing in code I had to touch, I'd have to break some fingers. There's absolutely no reason to be using extract() like that. You're creating 2 sets of variables for absolutely no good reason. Haven't you ever heard of list()?

while (list($items, $count) = mysql_fetch_row($res)) {



	switch ($items) {

	case "111111111111111":

		$all+=$count;

		break;

	case "000000000000000":

		break;

	case NULL:

		break;

	default:

		$some+=$count;

	}

}

Second, you could do what you want if you only select what you absolutely need from the database, which means using a where clause. Unless you like selecting every single record for some reason. Just wait until the DB has over 4000 records.

Did you even read the MySQL documentation on the SET column type before posting? There's a lot of useful information there. One of the user comments offered this link:

http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

But hey, I needed the morning stimulation.

I created the sample table in the article. Here's some sample queries based on that table:

// all rows in the column

// I'm uncertain why itemcount is turning up as NULL here, but not in the below example.

// Maybe a bug in MySQL?  Only thing different is a WHERE clause.

mysql> SELECT myset, myset+0, BIT_COUNT(myset+0) AS itemcount FROM set_test;

+-----------------------+---------+-----------+

| myset                 | myset+0 | itemcount |

+-----------------------+---------+-----------+

| Travel,Sports,Dancing |       7 |         3 |

| Travel,Sports,Dancing |       7 |         3 |

| Travel,Sports,Dancing |       7 |         3 |

| NULL                  |    NULL |      NULL |

| NULL                  |    NULL |      NULL |

| Travel                |       1 |      NULL |

| Sports                |       2 |      NULL |

| Dancing               |       4 |      NULL |

| Fine Dining           |       8 |      NULL |

| Travel                |       1 |      NULL |

+-----------------------+---------+-----------+

10 rows in set (0.00 sec)





// only rows in the column where myset has *something*

mysql> SELECT myset, myset+0, BIT_COUNT(myset+0) AS itemcount 

> FROM set_test WHERE myset IS NOT NULL;

+-----------------------+---------+-----------+

| myset                 | myset+0 | itemcount |

+-----------------------+---------+-----------+

| Travel,Sports,Dancing |       7 |         3 |

| Travel,Sports,Dancing |       7 |         3 |

| Travel,Sports,Dancing |       7 |         3 |

| Travel                |       1 |         1 |

| Sports                |       2 |         1 |

| Dancing               |       4 |         1 |

| Fine Dining           |       8 |         1 |

| Travel                |       1 |         1 |

+-----------------------+---------+-----------+

8 rows in set (0.01 sec)



// show the number of rows based on each possible combination found in the DB

mysql> SELECT myset, myset+0, BIT_COUNT(myset+0) AS itemcount, count(myset) 

> FROM set_test WHERE myset IS NOT NULL GROUP BY itemcount ORDER BY itemcount;

+-----------------------+---------+-----------+--------------+

| myset                 | myset+0 | itemcount | count(myset) |

+-----------------------+---------+-----------+--------------+

| Travel                |       1 |         1 |            5 |

| Travel,Sports,Dancing |       7 |         3 |            3 |

+-----------------------+---------+-----------+--------------+

2 rows in set (0.00 sec)





// simplified query to fetch only what we want

mysql> SELECT BIT_COUNT(myset+0) AS itemcount, count(myset) FROM set_test WHERE 

> myset IS NOT NULL GROUP BY itemcount ORDER BY itemcount;

+-----------+--------------+

| itemcount | count(myset) |

+-----------+--------------+

|         1 |            5 |

|         3 |            3 |

+-----------+--------------+

2 rows in set (0.01 sec)

P.S. I've never used the SET column type before this.

(edit: fixed wrapping)

(Edited by angry_chris on 09-19-2004 18:58)

Well, while we're all giving out friendly advice, here's a little for you:

First: See that title sitting under my name? That means I'm an administrator here. It doesn't usually pay to be disrespectful to anyone around an administrator, but anyway...

Second: Ever heard of this process called learning? It's what alot of people are doing most of the time. With learning comes mistakes and knowledge comes through figuring out those mistakes. Now, I know not everyone can be all knowing like you are, so maybe you've got to give people some leeway.

Third: We try to be friendly to everyone we can around here, especially when they've got a question we're trying to answer. The tone of your message, while it does contain pertinent information and so on, reeks of hostility. Since you're new here that's the kind of behaviour that quickly makes people cry 'troll'.

We're friendly peaceful people normally, but we do ask that if you're new you at least try and show some smidgin of respect to those who have been here longer than you. After all, it's a lot nicer to go through life respecting people until they prove they're not worthy of your respect. This is what i try to do, and I will let you know that you just took a big step towards my disrespectful column. Thankyou for your time.

Ouch... broken fingers for using extract() over list()... that's a tough penalty for such a small change.
I'm not quite sure how my usage of extract() creates more variables over a list() with the same 2 variables in it?

I did read the SET type documentation, (a lot ) and still couldn't come up with exactly what I wanted.

Your final query is better than mine, ie more efficient, and gets rid of the Null values from my revised version. Thanks for the help.

I'll continue my studies for a query that will remove that loop.

quote:

---

First: See that title sitting under my name? That means I'm an administrator here. It doesn't usually pay to be disrespectful to anyone around an administrator, but anyway...

---

That's about the only thing that consistenly makes me want to be be disrespectful to anyone right there^ A simple "thank you" would have sufficed.

My apologies then JK. I do try not to flaunt my position around here, but the whole tone of that post got up my nose.

Resolution for the future being to sit back and take a moment before I post, to actually think about how what I'm saying and, really, not to post jsut after I wake up.

Well, I do apologise to anyone who reads that then, really. I would edit my post to change my tone and make it say what I wanted it to say, but that owuldn't be the right thing. I shall attempt to think a bit further before next I post on such a subject.