New to UNIONS and GROUP BY

[mrhenniger]

When it comes to PHP and MySQL I am self taught.  I have learned the GROUP BY and COUNT feature of MySQL recently and have been satisfied with the results.  Now I am trying to use the MySQL command UNION and use the GROUP BY and COUNT features on the UNION table.  It is not working.  Let me work you up to the problem step-by-step.

 

Step #1 - Build the command for the first table in PHP...

$dataSN = "SELECT Event.AirframeSN AS ASN, Event.MilitarySerial AS MSN, Air.FamilySN AS FSN " .

    "FROM Event " .

    "LEFT JOIN Air " .

    "ON Event.AirframeSN = Air.Serial " .

    "WHERE Event.MilitarySerial NOT LIKE \"\"";

If I execute this in MySQL on it's own I get a table of size 1,326.  That is about right, so I am confident in that number.

 

Step #2 - Build the command for the second table in PHP...

$dataCN = "SELECT Event.AirframeSN AS ASN, (TRIM(LEADING '?' FROM Air.ConstructionNumber)) AS MSN, Air.FamilySN AS FSN " .

    "FROM Event " .

    "LEFT JOIN Air " .

    "ON Event.AirframeSN = Air.Serial " .

    "WHERE Air.ConstructionNumber LIKE \"?%\"";

If I execute this in MySQL on it's own I get a table size of 62,621.  That is about right as well, so no worries there either.

 

Step #3 - Merge the two tables into one...

$theUnion = "(" . $dataSN . ") UNION (" . $dataCN . ")"; // ASN, MSN, FSN

When I execute this in MySQL on it's own I get a table size of 15,551.  Considering UNION example removes dupliciate rows this number is about right.  So I have confident with this result as well.

 

Step #4 - Here comes the part that I didn't expect to be having problems with.  I combine the PHP strings previously tested individually into one command which is supposed to group the data and count the number of items in each group...

$command = "SELECT ASN, MSN, FSN, " .

    "CONCAT(MSN, '---', CAST(FSN AS CHAR)) as grouper, " .

    "COUNT(ASN) AS cnt " .

    "FROM (" . $theUnion . ") " .

    "GROUP BY grouper HAVING cnt > 1 " .

    "ORDER BY FSN, cnt DESC";

When this command is attempted MySQL fails to execute it, it wasn't a case of no results matching, but rather was a hard failure.  There is no error message provided either (sometimes that happens).

 

I thought I would have had problems with the UNION (newest part to me), but it looks like I got the GROUP BY and COUINT feature wrong.

 

Can anyone suggest where I may have made a mistake?  I am to the point where starring at the code isn't giving me any new ideas of things to try.

 

Thanks in advance.

 

Mike

 

[kickstart]

Hi

 

Without delving too deep, one issue is that when you use a subselect as a FROM clause you need to give it an alias name:-

 

$command = "SELECT ASN, MSN, FSN, " .

        "CONCAT(MSN, '---', CAST(FSN AS CHAR)) as grouper, " .

        "COUNT(ASN) AS cnt " .

        "FROM (" . $theUnion . ") a " .

        "GROUP BY grouper HAVING cnt > 1 " .

        "ORDER BY FSN, cnt DESC";

 

All the best

 

Keith

[mrhenniger]

That worked.  Thanks!

 

Mike