如何显示分组列的总收入和月收入？(MySQL)

Question

我的数据库中有这张表：我需要一个显示总成本、每月总成本、每月计数（roomId）、总计数（roomId）的查询，按房间 ID 分组。我试过这个查询，但结果并不像我需要的SELECT room_id, type, numberOfBeds,        (SELECT SUM(total_cost)          WHERE date_booked BETWEEN '".$dateFrom."' and '".$dateTo."') as monthlyIncome,        SUM(total_cost) as totalIncome,       COUNT(roomId) as totalReservations,       (SELECT COUNT(roomId)          WHERE date_booked BETWEEN '".$dateFrom."' and '".$dateTo."') as monthlyReservation   FROM indvproj_rooms, indvproj_room_booking_details WHERE indvproj_rooms.room_id=indvproj_room_booking_details.roomId;

Answer 1

问题不清楚。当你做 SUM 时，每月成本和总成本有什么区别？请检查以下查询和结果是否与您需要的相同。

SELECT

   * 

FROM

   (

      SELECT

         roomId,

         SUM(total_cost) as monthly_cost,

         COUNT(roomId) as monthly_count 

      FROM

         room 

      group by

         roomId,

         YEAR(date_booked),

         MONTH(date_booked) 

   )

   as r 

   LEFT JOIN

      (

         SELECT

            roomId as rid,

            SUM(total_cost) as total_cost,

            COUNT(roomId) as total_count 

         FROM

            room 

         group by

            roomId

      )

      as t 

      on r.roomId = t.rid