Is there a way to optimize this mysql query...?

Say, I got these two tables....

Table 1 : Hotels
   hotel_id   hotel_name
       1          abc
       2          xyz
       3          efg

Table 2 : Payments
    payment_id     payment_date     hotel_id     total_amt    comission
       p1           23-03-2010          1          100           10
       p2           23-03-2010          2          50            5
       p3           23-03-2010          2          200           25
       p4           23-03-2010          1          40            2

Now, I need to get the following details from the two tables

1. Given a particular date (say, 23-03-2010), the sum of the total_amt for each of the hotel for which a payment has been made on that particular date.
2. All the rows that has the date 23-03-2010 ordered according to the hotel name

A sample output is as follows...

+------------+------------+------------+---------------+
| hotel_name |   date     |  total_amt |   commission  |
+------------+------------+------------+---------------+
| * abc      | 23-03-2010 |     140    |      12       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p1      | 23-03-2010 |     100    |     10       ||
|+-----------+------------+------------+--------------+|
||   p4      | 23-03-2010 |     40     |     2        ||
|+-----------+------------+------------+--------------+|
+------------+------------+------------+---------------+
| * xyz      | 23-03-2010 |     250    |      30       |
+------------+------------+------------+---------------+
|+-----------+------------+------------+--------------+|
|| paymt_id  |   date     |  total_amt |   commission ||
|+-----------+------------+------------+--------------+|
||   p2      | 23-03-2010 |     50     |      5       ||
|+-----------+------------+------------+--------------+|
||   p3      | 23-03-2010 |     200    |      25      ||
|+-----------+------------+------------+--------------+|
+------------------------------------------------------+

Above the sample of the table that has to be printed...

The idea is first to show the consolidated detail of each hotel, and when the '*' next to the hotel name is clicked the breakdown of the payment details will become visible... But that can be done by some jquery..!!! The table itself can be generated with php...

Right now i am using two separate queries : One to get the sum of the amount and commission grouped by the hotel name. The next is to get the individual row for each entry having that date in the table. This is, of course, because grouping the records for calculating sum() returns only one row for each of the hotel with the sum of the amounts...

Is there a way to combine these two queries into a single one and do the operation in a more optimized way...??

Hope i am being clear.. Thanks for your time and replies...

EDIT : Added Queries Too

1. Query to get the sum()

    
   SELECT DATE_FORMAT($payments.payment_date, '%d-%m-%Y %T') as payment_date,
           $payments.payment_id AS payment_id,
           $payments.payment_amount AS payment_amount,
           $payments.agent_commision AS commision,
           $payments.comm_percentage AS percentage,
           $hotels.hotel_name AS hotel
           SUM($payments.payment_amount)  AS tot_amt
           SUM($payments.agent_commision) AS tot_ag_comsn
   FROM $payments
   JOIN $hotels
   ON   $payments.hotel_id = $hotels.hotel_id
   WHERE DATE_FORMAT(payment_date,'%d-%m-%Y') = '$date_report'
   GROUP BY $payments.hotel_id
   ORDER BY $payments.payment_date ASC 
   

2. Query to get the individual rows

      
   SELECT DATE_FORMAT($payments.payment_date, '%d-%m-%Y %T') as payment_date,
               $payments.payment_id AS p开发者_开发百科ayment_id,
               $payments.payment_amount AS payment_amount,
               $payments.agent_commision AS commision,
               $payments.comm_percentage AS percentage,
               $hotels.hotel_name AS hotel
   FROM $payments
   JOIN $hotels
   ON   $payments.hotel_id = $hotels.hotel_id
   WHERE DATE_FORMAT(payment_date,'%d-%m-%Y') = '$date_report'
   GROUP BY $payments.payment_date
   ORDER BY $payments.payment_date ASC
    

   I add the rows returned by the two queries in two separate temporary tables and then use php to print the table as shown above...

EDIT 2 : And also, appreciate if someone suggests a better title for this post... ;)

---

This is the domain of report writers, or equivalent logic in a programming language.