There is a table

  • ladder (this is the id of the product from another table, in this table is supposedly a variety)

  • size (these are sizes)

  • lenght (this is the length, several sizes correspond to the same length)

I need to get for a specific product (the maximum number of sizes with one length).

I.e

ladder size lenght 1 10 100 1 20 100 1 30 100 1 40 100 1 10 200 1 20 200

I have to get:

 count 4

If the item is not one, then this will be the table:

 count 4 2 6 3

The table is not a big maximum of 200 records. Will it be possible to do this in one query?

  • SELECT sum (*) From 'tablename' GROUP BY 'length' - E_p
  • I advise you to index by length. While the table is small. - E_p

1 answer 1

We group the subquery by product and length, count the number of sizes of the goods. The result is grouped by product and select the maximum quantity.

 SELECT ladder, max(size_count) FROM (SELECT ladder, count(DISTINCT size) size_count FROM properties GROUP BY ladder, lenght) t GROUP BY t.ladder;

sqlfiddle