Closed Thread Icon

Topic awaiting preservation: mySQL: trying to count rows where bit_count in SET > 0 (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=23337" title="Pages that link to Topic awaiting preservation: mySQL: trying to count rows where bit_count in SET &amp;gt; 0 (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: mySQL: trying to count rows where bit_count in SET &gt; 0 <span class="small">(Page 1 of 1)</span>\

 
PaulBM
Nervous Wreck (II) Inmate

From: East Anglia, England.
Insane since: Sep 2003

posted posted 09-17-2004 11:18

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...

code:
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.

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

posted posted 09-17-2004 16:35

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.

PaulBM
Nervous Wreck (II) Inmate

From: East Anglia, England.
Insane since: Sep 2003

posted posted 09-18-2004 11:41

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.

code:
"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;
}
}

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

posted posted 09-18-2004 12:19

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:

code:
$count = mysql_num_rows($result);

angry_chris
Neurotic (0) Inmate
Newly admitted

From:
Insane since: Sep 2004

posted posted 09-19-2004 18:55

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()?

code:
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:

code:
// 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)

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

posted posted 09-20-2004 10:15

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.

PaulBM
Nervous Wreck (II) Inmate

From: East Anglia, England.
Insane since: Sep 2003

posted posted 09-20-2004 10:40

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.

JKMabry
Maniac (V) Inmate

From: raht cheah
Insane since: Aug 2000

posted posted 09-20-2004 16:58
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.

Skaarjj
Maniac (V) Mad Scientist

From: :morF
Insane since: May 2000

posted posted 09-20-2004 17:45

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.

« BackwardsOnwards »

Show Forum Drop Down Menu