This is the original table: “multiactualusetest”
| TAXDECNUM | ACTUALUSE | TAXABLE | MARKETVALUE | ASSESSEDVALUE |
| ————- | ————- | ————- | ————- | ————- |
| G-508659 | S | Y | 182000 | 0 |
| G-508659 | C | Y | 166600 | 16660 |
| G-508659 | A | Y | 9768 | 3910 |
| G-508659 | R | Y | 140000 | 9800 |
| G-432185 | R | Y | 65000 | 4550 |
| G-432185 | A | Y | 9041.09 | 3620 |
| E-008926 | A | Y | 20346.12 | 940 |
| E-008926 | A | Y | 7360 | 540 |
| E-008926 | R | Y | 27000 | 5500 |
| G-508847 | R | Y | 21500 | 1510 |

From the multiactualusetest table above, the following table was created using this query:

SELECT TAXDECNUM, ACTUALUSE, TAXABLE, SUM(MARKETVALUE) AS TOTAL_MARKETVALUE, SUM(ASSESSEDVALUE) AS TOTAL_ASSESSEDVALUE FROM multiactualusetest GROUP BY TAXDECNUM, ACTUALUSE ORDER BY TAXDECNUM, ACTUALUSE

What it did was to sum up the MARKETVALUE and ASSESSEDVALUE of all similar ACTUALUSE from each TAXDECNUM. In the multiactualusetest table, only TAXDECNUM rows containing E-008926 has 2 similar ACTUALUSE rows of “A”. So, the following resulting resultset shows it.

Now, I want another result set that will show the following columns:

TAXDECNUM, ACTUALUSE, COUNTX, TAXABLE, MARKETVALUE, ASSESSEDVALUE

where the COUNTX is 1 when the TOTAL_MARKETVALUE is maximum for each TAXDECNUM. All the other COUNTX for the non-maximum TOTAL_MARKETVALUE values are 0.

The result should be as follows:

Note: Such keywords as** CTE, OVER, PARTITION**…it doesn’t work as I’m using PHPMYADMIN MySQL. So, I think these should probably consists of queries on subqueries.

Please help.

The AI sites I sought help generated query statements that involved creating temporary tables like the following:

SELECT TAXDECNUM, ACTUALUSE, TAXABLE, SUM(MARKETVALUE) AS TOTAL_MARKETVALUE, SUM(ASSESSEDVALUE) AS TOTAL_ASSESSEDVALUE 
INTO #TempTable FROM `multiactualusetest` GROUP BY TAXDECNUM, ACTUALUSE

SELECT TAXDECNUM, ACTUALUSE, CASE WHEN TOTAL_MARKETVALUE = (SELECT MAX(TOTAL_MARKETVALUE) FROM #TempTable AS t2 WHERE t2.TAXDECNUM = t1.TAXDECNUM) THEN 1 ELSE 0 END AS COUNTX, 
    TAXABLE, TOTAL_MARKETVALUE, TOTAL_ASSESSEDVALUE
FROM #TempTable AS t1
ORDER BY TAXDECNUM, ACTUALUSE

DROP TABLE #TempTable

This did not work either as PhpMyAdmin doesn’t seem to like multiple separate query statements lumped into one.