Click here to Skip to main content
14,971,029 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This page is to view on the most popular program in the most popular cities, so below are the code for that. The output of the most popular cities works fine but the most popular program in those cities displaying incorrect output. Can someone assist to check in my query if there is any mistake? Thank you very much for your time.

What I have tried:

<pre><table class="table table-striped table-dark">
<thead>
 <tr>
            <td>City</td>
            <td>Popular Programme</td>
        </tr>
    </thead>
         <tbody> 
<?php
                         $query = " SELECT student_city, count(*) AS count, student_prg FROM marketing_data GROUP BY student_city ORDER by count desc LIMIT 3;";
                if(count(fetchAll($query))>0){
                    foreach(fetchAll($query) as $row){
                        ?>
                    <tr>
                    <td><?php echo $row['student_city'] ?></td>
                      <td><?php echo $row['student_prg'] ?></td>
                      </tr>             
            <?php
                    }
                }else{
                    echo "<script>alert('No record')</script>";
                }
            ?>
         </tbody>  
      </table>


Table Structure
CREATE TABLE marketing_data
(
student_matric VARCHAR NOT NULL AUTO_INCREMENT,
student_prg TEXT NOT NULL,
semester VARCHAR(10) NOT NULL,
intake_session INT(1) NOT NULL,
intake_year INT(10) NOT NULL,
student_city TEXT NOT NULL,
city_lat VARCHAR(20) NOT NULL,
city_long VARCHAR(20) NOT NULL,
student_state TEXT NOT NULL,
state_code VARCHAR(100) NOT NULL
); 


Output from MySQL console:
+---------------+------------------+------+-----+---------+-----------------
| student_city  | count |        student_prg              |
+---------------+------------------+------+-----+---------+----------------+
| Kuala Dungun  |   4   | Bachelor of Multimedia Industry |
|   Jerantut    |   3   | Bachelor of Multimedia Industry |
|Bukit Beruntung|   3   | Bachelor of Software Engineering|
+---------------+------------------+------+-----+---------+----------------+
Posted
Updated 21-Jun-20 2:45am
Comments
[no name] 21-Jun-20 8:39am
   
The problem I think comes from student_prg. At least MS SQL will not even gives you a result, it will give an error message something like 'invalid field' because it is not an aggregate.
Have a read e.g. here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
Member 14865416 21-Jun-20 8:44am
   
Hi, thank you for your reply. May I know what is the correct query to execute the expected output? Because there was no error message when I run it in MySQL console.
[no name] 21-Jun-20 8:49am
   
Allow me first a question: Does MySQL console really give a result without error message for the _exactly same_ SQL statement? Hard to belive...

Member 14865416 21-Jun-20 8:55am
   
Yes, it does as mentioned above. I couldn't attach an image here, so I couldn't share the screencapture.
[no name] 21-Jun-20 9:07am
   
Very strange.
Now what is the correct query? It depends....
In case student_prg can be different for each group by student_city the only I see is GROUP BY student_city, student_prg. But looks like this is not what you like to have as result.
Member 14865416 21-Jun-20 9:16am
   
Yes, exactly. GROUP BY student_city, student_prg works as showing the cities that have a similar program. For an example, Gua Musang have 2 records and both records are taking Bachelor of Multimedia Industry. So the system only pick that, it doesn't count first which is the most popular city.
[no name] 21-Jun-20 9:31am
   
Do I understand it right? You like to have the three most popular city/prg and having the city only once in the result? (oh god my English is that bad I hope you understand what I mean...)
Member 14865416 21-Jun-20 12:03pm
   
That's ok, I can understand you... I like to have most popular program in the most popular cities. First, the city should be the most popular comparing with other cities. Secondly, the program in that city must be most popular comparing with other program in that city.
Member 14865416 21-Jun-20 12:12pm
   
I just found out a solution with this query SELECT student_city as original_student_city, count(*) AS count, (select student_prg from marketing_data where student_city = original_student_city group by student_prg order by count(student_prg) desc limit 1) as new_student_prg FROM marketing_data GROUP BY student_city ORDER by count desc LIMIT 3; and it works perfectly now. Thank you for your time anyway. Have a nice day....
[no name] 21-Jun-20 12:35pm
   
:thumbsup:

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900