Hi everyone, I have a strange question for you. Is it possible to return the count of columns that meet a condition just as you can count rows? The engine I'm using is MySQL.

Here is my table structure:

CREATE TABLE intelligent_offer (
  products_id int(11) unsigned NOT NULL default '0',
  pid1 int(11) unsigned NOT NULL default '0',
  score1 float(11,3) NOT NULL default '0.000',
  pid2 int(11) unsigned NOT NULL default '0',
  score2 float(11,3) NOT NULL default '0.000',
  pid3 int(11) unsigned NOT NULL default '0',
  score3 float(11,3) NOT NULL default '0.000',
  pid4 int(11) unsigned NOT NULL default '0',
  score4 float(11,3) NOT NULL default '0.000',
  pid5 int(11) unsigned NOT NULL default '0',
  score5 float(11,3) NOT NULL default '0.000',
  pid6 int(11) unsigned NOT NULL default '0',
  score6 float(11,3) NOT NULL default '0.000',
  pid7 int(11) unsigned NOT NULL default '0',
  score7 float(11,3) NOT NULL default '0.000',
  pid8 int(11) unsigned NOT NULL default '0',
  score8 float(11,3) NOT NULL default '0.000',
  pid9 int(11) unsigned NOT NULL default '0',
  score9 float(11,3) NOT NULL default '0.000',
  pid10 int(11) unsigned NOT NULL default '0',
  score10 float(11,3) NOT NULL default '0.000',
  PRIMARY KEY  (products_id)
) ENGINE=MyISAM;

Here is what I want to do: I want to return 100 pidXX values that are greater than 0 so I can display the results in a paging list.

Most rows have all 10 pidXX columns over zero value, but there are many that don't.

Any suggestions or ideas how I could tackle this problem another way?

Thank you!

Could you provide the exact question you're trying to ask the database? I'm a bit confused on what you're wanting: Do all pidXX columns need to be > 0? A subset? Or, are you searching by, for example, pid9 that you want to be > 0?

Unpivot the table to get a representation as rows, instead of columns.

Oh. I just re-read your post. Let me think about it for a bit; but, just to make sure, what you want to do is return each row with at least one non-zero in it, but only so many rows that the count of all non-zeroes across all rows == 100. In something closer to English. If the first row had 2 of its values as non-zero and the second row and 3 non-zeroes, then those 2 rows would count for 5 of the 100 results required.

OK. Here's what I came up with.

To get the total number of entries per row with a pidxx value > 0, use

SELECT (pid1>0)+(pid2>0)+(pid3>0)+(pid4>0)+(pid5>0)+(pid6>0)+(pid7>0)+(pid8>0)+(pid9>0)+(pid10>0)  as col_count FROM `intelligent_offer`

The problem is that I don't think you can filter based on a rolling sum (though maybe a stored procedure or function would help?)

Based on what I understand of your requirements, you may want to try a table structure like this:

CREATE TABLE intelligent_offer (
  int_off int(10) unsigned NOT NULL auto_increment,
  entry_num enum('1','2','3','4','5','6','7','8','9','10') NOT NULL default '1',
  pid int(10) unsigned NOT NULL,
  score float unsigned NOT NULL,
  PRIMARY KEY  (int_off)
) ENGINE=MyISAM;

entry_num's type could be changed and you'd be able to store even more ids, plus the way to answer your question with that structure (and the SQL I gave above) provides more power.

Hope that helps.

Let me try to clear up my explaination:

With the data structure above, I have an automated script that fills the table with other browsed and shopped items related to each product item from our analytics data feed. Each product_id will at least have 1 pidXX with a related id number, but not necessarly will have all 10 pidXX columns filled (they will default to zereo value).

Now I want to build a "Top Sellers" page from the data from this table and want to show the top 100 pidXX items using pagination. How could I define that in the query? If a pidXX value is zereo, I don't want that counted as part of the 100 results.


Thanks for the help - I hope this may answer your questions!

Hmmm...Maybe sum up the matching values in each row and then order by that? Is that what you're talking about? I feel kinda dumb. I know I can help. I just don't get it. Must be a day to be dense for me.

Couldn't you just get an array length from your result set?

This could (may) be of use to you: http://www.jumbabox.com/2008/06/usin...base-tutorial/

Originally Posted by JeremyMiller

Hmmm...Maybe sum up the matching values in each row and then order by that? Is that what you're talking about? I feel kinda dumb. I know I can help. I just don't get it. Must be a day to be dense for me.

This is similar to the option I was thinking. Let me give you a sample list of row data so you can see whar I am talking about. These rows go with the data structure above.

INSERT INTO `intelligent_offer` VALUES (10759, 10362, 17998.561, 4155, 17998.561, 10752, 13998.880, 10460, 11999.040, 10654, 11999.040, 10750, 11999.040, 10756, 11999.040, 11284, 7999.360, 10016, 5999.520, 10033, 5999.520);
INSERT INTO `intelligent_offer` VALUES (10760, 10613, 17998.561, 10642, 17998.561, 10750, 17998.561, 10753, 17998.561, 10757, 17998.561, 13014, 17998.561, 13257, 17998.561, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10761, 10084, 8999.280, 10364, 8999.280, 10654, 8999.280, 10753, 8999.280, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10763, 10706, 7023.940, 12973, 6584.940, 10781, 5121.620, 11308, 4389.960, 0, 0.000, 0, 40.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10764, 10903, 10974.900, 10728, 9755.470, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10766, 11512, 4703.530, 10770, 4703.530, 12954, 3135.690, 11455, 3135.690, 10772, 3135.690, 10620, 3135.690, 13071, 3135.690, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10769, 10773, 6584.940, 12706, 4389.960, 10901, 4268.020, 9611, 3658.300, 15760, 3658.300, 15764, 3658.300, 2025, 3658.300, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10770, 12954, 10974.900, 10773, 7316.600, 10778, 5487.450, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10771, 12727, 6584.940, 12932, 6584.940, 10777, 5853.280, 12091, 4389.960, 14543, 4389.960, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10772, 11580, 4115.590, 12104, 4115.590, 10722, 3919.610, 10629, 3658.300, 10769, 3658.300, 15787, 3658.300, 9066, 3292.470, 10766, 3135.690, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10773, 11495, 14633.200, 10911, 7316.600, 10770, 7316.600, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000, 0, 0.000);
INSERT INTO `intelligent_offer` VALUES (10777, 3823, 10974.900, 5588, 7316.600, 4716, 7316.600, 8674, 7316.600, 4343, 7316.600, 2586, 7316.600, 10771, 5853.280, 10778, 5487.450, 7584, 5487.450, 4699, 4877.730);
INSERT INTO `intelligent_offer` VALUES (10778, 12615, 8231.180, 14543, 5487.450, 10777, 5487.450, 10770, 5487.450, 10764, 3658.300, 10771, 3292.470, 12932, 2743.730, 12596, 2743.730, 0, 0.000, 0, 0.000);

From this list, could you return 50 of the highest scoring pidXX values?

Originally Posted by VirtuosiMedia

Couldn't you just get an array length from your result set?

I may just have to revert to something this simple if I can't get the db to do it.

Originally Posted by tripy

This could (may) be of use to you: http://www.jumbabox.com/2008/06/usin...base-tutorial/

Thanks for the link, but it was over my head and worked with excell spreadsheet (?)

Originally Posted by mgraphic

I may just have to revert to something this simple if I can't get the db to do it.

To get the database to do it without writing miles of SQL code, you need to express the data as rows, not columns. There's no other way, that doesn't involve enough text to fill a hardcover.

An unpivot query is the easiest way to do this. Failing that, you can use SQL to write and then execute a series of union subqueries, such that the data will be presented as

ProductID, PID, Score
ProductID, PID, Score
ProductID, PID, Score
ProductID, PID, Score

So, Learning, the alternate table structure I provided above, right? It seemed to me like the database needed to be normalized to answer the query being asked. It's an easy question with the other table structure, very hard with the current.

Originally Posted by JeremyMiller

So, Learning, the alternate table structure I provided above, right? It seemed to me like the database needed to be normalized to answer the query being asked. It's an easy question with the other table structure, very hard with the current.

Yours was the perfect answer, and I would guess your table structure will pose other benefits beyond this one.

Originally Posted by Learning Newbie

To get the database to do it without writing miles of SQL code, you need to express the data as rows, not columns. There's no other way, that doesn't involve enough text to fill a hardcover.

An unpivot query is the easiest way to do this. Failing that, you can use SQL to write and then execute a series of union subqueries, such that the data will be presented as

OK - I will look into this, unpivot query is a brand new concept for me, I have never heard of it until now. I'm always and forever learning!

We're all forever learning - as long as we're open to it!

Here's something I copied from the MS documentation. I'm not sure where to find the corresponding MySQL?

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.
Copy Code

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
Here is a partial result set.
Copy Code

VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5

Well - I have been derailed and put on other projects so I have not had the chance to try these solutions yet. Thank you for your help though.

You help is appreciated, thanks for sharing your knowledge. I wanted to give TP before it got too late (I wish I could give 2-fold).