Select data which have same letters

Question

I'm having trouble with this SQL:

$sql = mysql_query("SELECT $menucompare ,
  (COUNT($menucompare ) * 100 / (SELECT COUNT( $menucompare )
  FROM data WHERE $ww = $button ))  AS percentday FROM data WHERE $ww >0  ");

• $menucompare is table fields names what ever field is selected and contains data bellow
• $button is the week number selected (lets say week '6')
• $ww table field name with row who have the number of week '6'

For example, I have data in $menucompare like that:

123456bool
521478bool
122555heel
147788itoo

and I want to select those, who have same word in the last of the data and make percentage.

The output should be like that:

• bool -- 50% (2 entries)
• heel -- 25% (1 entry)
• itoo -- 25% (1 entry)

Any clearness to my SQL will be very appreciated. I didn't find anything like that around.

Answer 1

Well, keeping data in such format probably not the best way, if possible, split the field into 2 separate ones.

First, you need to extract the string part from the end of the field.

• if the length of the string / numeric parts is fixed, then it's quite easy;
• if not, you should use regular expressions which, unfortunately, are not there by default with MySQL. There's a solution, check this question: How to do a regular expression replace in MySQL?

I'll assume, that numeric part is fixed:

SELECT s.str, CAST(count(s.str) AS decimal) / t.cnt * 100 AS pct
  FROM (SELECT substr(entry, 7) AS str FROM data) AS s
  JOIN (SELECT count(*) AS cnt FROM data) AS t ON 1=1
 GROUP BY s.str, t.cnt;

If you'll have regexp_replace function, then substr(entry, 7) should be replaced to regexp_replace(entry, '^[0-9]*', '') to achieve the required result.

Variant with substr can be tested here.

Answer 2

When sorting out problems like this, I would do it in two steps:

1. Sort out the SQL independently of the presentation language (PHP?).
2. Sort out the parameterization of the query and the presentation of the results after you know you've got the correct query.

Since this question is tagged 'SQL', I'm only going to address the first question.

The first step is to unclutter the query:

SELECT menucompare,
       (COUNT(menucompare) * 100 / (SELECT COUNT(menucompare) FROM data WHERE ww = 6))
          AS percentday
  FROM data
 WHERE ww > 0;

This removes the $ signs from most of the variable bits, and substitutes 6 for the button value. That makes it a bit easier to understand.

Your desired output seems to need the last four characters of the string held in menucompare for grouping and counting purposes.

The data to be aggregated would be selected by:

SELECT SUBSTR(MenuCompare, -4) AS Last4
  FROM Data
 WHERE ww = 6

The divisor in the percentage is the count of such rows, but the sub-stringing isn't necessary to count them, so we can write:

SELECT COUNT(*) FROM Data WHERE ww = 6

This is exactly what you have anyway.

The divdend in the percentage will be the group count of each substring.

SELECT Last4, COUNT(Last4) * 100.0 / (SELECT COUNT(*) FROM Data WHERE ww = 6)
  FROM (SELECT SUBSTR(MenuCompare, -4) AS Last4
          FROM Data
         WHERE ww = 6
       ) AS Week6
 GROUP BY Last4
 ORDER BY Last4;

When you've demonstrated that this works, you can re-parameterize the query and deal with the presentation of the results.