然后计算PHP和MySQL中的排序函数

Question

I have this database wherein it has columns named Mall (it contains different names of Malls), NameOfStore (it contains name of stores) and AMEX (it contains either YES or NO depending if the store accepts American Express or not).

What I want to do is to count the number of stores accepting AMEX for each mall and then sort the malls in a descending manner according to their number of stores accepting AMEX. I want to incorporate a "Sorting" feature in a dropdown list form where users of my program can choose if they want to sort the malls with the most AMEX, etc. I was able to make a dropdown list for my "filter according to some condition" feature but this Sorting feature is much different.

I'm not sure on how I'm going to merge and properly sequence the logic for these queries.

 function sortStore($conn, $sort)
    {
    $table = "table1";
    $column1 = "Mall";
    $column2 = "NameOfStore";
    $condition = "AMEX = 'YES'"

    $query = Select Count($column2) from $table where $condition AND $sort";
    ..other codes..
    //$sort would contain cases (one case for each mall)
    //Example: case "Mall1": $sort = "Mall = 'Mall1'; break;
    //I'm actually not sure if the logic on my query is correct based on what I want to happen
    }

Then I would want to ORDER the Malls by the Number of Stores they have that are accepting AMEX in a descending order. I need to use ORDER BY function but I'm not sure where to place it in this particular context.

SUMMARY:

Dropdown list

User chooses what category he wants the mall to be sorted (Example: Number of Stores with AMEX, Number of Restaurants, etc.)

Program displays ordered list of Malls for the chosen category

Answer 1

This query should give you the desired result.

select mall, count(AMEX) AS AMXCNT 
from table1 
WHERE AMEX='yes' 
GROUP BY MALL ORDER BY AMXCNT