mysql - 带有子查询的 SQL SUM 查询

Question

我试图获取每个站点的总“容量”，但我不断收到错误消息“#1630 - FUNCTION tflBikes.COUNT 不存在”:

SELECT Count(ts.capacity),
       tbu.stationid,
       ts.name,
       ts.easting,
       ts.northing,
       tbu.t,
       Round(Avg(availablebikes), 1) AS Average,
       Round(Avg(availablebikes) / capacity * 100, 1) AS Percentage,
       ts.postcode
FROM tflbikeusage tbu,
     tflstations ts
WHERE tbu.stationid = ts.usageid
  AND ts.easting = Abs(easting -
                         (SELECT easting
                          FROM tflstations
                          WHERE name = 'Hatton Garden, Holborn')) < 750 * 0.5
  AND ts.northing = Abs(northing -
                          (SELECT northing
                           FROM tflstations
                           WHERE name = 'Hatton Garden, Holborn')) < 750 * 0.5
  AND Hour(t) BETWEEN 10 AND 22
GROUP BY ts.capacity HAVING COUNT (ts.capacity) =
  (SELECT Sum(capacityno)
   FROM
     (SELECT COUNT(ts.capacity) AS capacityNo,
                  tbu.stationid,
                  ts.name,
                  ts.easting,
                  ts.northing,
                  tbu.t,
                  Round(Avg(availablebikes), 1) AS Average,
                  Round(Avg(availablebikes) / capacity * 100 , 1) AS Percentage,
                  ts.postcode
      FROM tflbikeusage tbu,
           tflstations ts
      WHERE tbu.stationid = ts.usageid
        AND ts.easting = Abs(easting -
                               (SELECT easting
                                FROM tflstations
                                WHERE name = 'Hatton Garden, Holborn')) < 750 * 0.5
        AND ts.northing = Abs(northing -
                                (SELECT northing
                                 FROM tflstations
                                 WHERE name = 'Hatton Garden, Holborn')) < 750 * 0.5
        AND Hour(t) BETWEEN 10 AND 22
      GROUP BY ts.capacity
      ORDER BY percentage DESC)derivedTable)

Answer 1

您将 tflbikesUsage 别名为“tbu”，因此您应该继续使用它。