Пытаюсь выдернуть отчет по трафику за два месяца по схеме когда за предыдущие месяцы хранится в отдельных таблицах, к примеру
февраль discount_transactions_iptraffic_all, а январь discount_transactions_iptraffic_all_1233435600, пытаюсь выдернуть запросом (SELECT t_class, base_cost, SUM(bytes), SUM(discount) FROM discount_transactions_iptraffic_all WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140 UNION ALL SELECT t_class, base_cost, SUM(bytes), SUM(discount) FROM discount_transactions_iptraffic_all_1233435600 WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140) GROUP BY t_class,base_cost order by t_class, base_cost
ошибка ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUPBY t_class,base_cost order by t_class, base_cost' at line 1
может ктонидь решал задачу???
Отчет по трафику по схеме 006
А вот так если:
Читал вот это http://dev.mysql.com/doc/refman/5.0/en/union.html
Код: Выделить всё
(SELECT t_class, base_cost, SUM(bytes), SUM(discount) FROM discount_transactions_iptraffic_all WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140) UNION ALL (SELECT t_class, base_cost, SUM(bytes), SUM(discount) FROM discount_transactions_iptraffic_all_1233435600 WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140) GROUP BY t_class,base_cost order by t_class, base_cost;
Код: Выделить всё
(SELECT discount_date, SUM(bytes) as 'bytes', t_class,
MONTH FROM_UNIXTIME(discount_date)) AS 'month',
YEAR(FROM_UNIXTIME(discount_date)) AS 'year'
FROM UTM5H.discount_transactions_iptraffic_all_200901
WHERE 1
AND account_id = 3351
AND (discount_date BETWEEN '1230750000' AND '1262286058')
AND t_class <> 0
GROUP BY year, month, t_class
ORDER BY year, month, t_class)
UNION ALL
(SELECT discount_date, SUM(bytes) as 'bytes', t_class,
MONTH(FROM_UNIXTIME(discount_date)) AS 'month',
YEAR(FROM_UNIXTIME(discount_date)) AS 'year'
FROM UTM5.discount_transactions_iptraffic_all
WHERE 1
AND account_id = 3351
AND (discount_date BETWEEN '1230750000' AND '1262286058')
AND t_class <> 0
GROUP BY year, month, t_class
ORDER BY year, month, t_class)
Спасибо все зашибись, но... необходимо просуммировать предыдущий и этот месяц у одинаковых классов трафика... а то он их раздельно группирует. Как это сделать запросом?Magnum72 писал(а):Код: Выделить всё
(SELECT discount_date, SUM(bytes) as 'bytes', t_class, MONTH FROM_UNIXTIME(discount_date)) AS 'month', YEAR(FROM_UNIXTIME(discount_date)) AS 'year' FROM UTM5H.discount_transactions_iptraffic_all_200901 WHERE 1 AND account_id = 3351 AND (discount_date BETWEEN '1230750000' AND '1262286058') AND t_class <> 0 GROUP BY year, month, t_class ORDER BY year, month, t_class) UNION ALL (SELECT discount_date, SUM(bytes) as 'bytes', t_class, MONTH(FROM_UNIXTIME(discount_date)) AS 'month', YEAR(FROM_UNIXTIME(discount_date)) AS 'year' FROM UTM5.discount_transactions_iptraffic_all WHERE 1 AND account_id = 3351 AND (discount_date BETWEEN '1230750000' AND '1262286058') AND t_class <> 0 GROUP BY year, month, t_class ORDER BY year, month, t_class)
Код: Выделить всё
(SELECT t_class, base_cost, SUM(bytes) as 'bytes', SUM(discount) as 'discount' FROM discount_transactions_iptraffic_all WHERE account_id = 140 AND (discount_date BETWEEN '1232888220' AND '1235566679') AND t_class <> 0 GROUP BY t_class,base_cost,account_id order by account_id, t_class, base_cost) UNION ALL (SELECT t_class, base_cost, SUM(bytes) as 'bytes', SUM(discount) as 'discount' FROM discount_transactions_iptraffic_all_1233435600 WHERE account_id = 140 AND (discount_date BETWEEN '1232888220' AND '1235566679') AND t_class <> 0 GROUP BY t_class,base_cost,account_id order by account_id, t_class, base_cost) 
В PHP суммируйте.Davion писал(а):вот его надо подделатьКод: Выделить всё
(SELECT t_class, base_cost, SUM(bytes) as 'bytes', SUM(discount) as 'discount' FROM discount_transactions_iptraffic_all WHERE account_id = 140 AND (discount_date BETWEEN '1232888220' AND '1235566679') AND t_class <> 0 GROUP BY t_class,base_cost,account_id order by account_id, t_class, base_cost) UNION ALL (SELECT t_class, base_cost, SUM(bytes) as 'bytes', SUM(discount) as 'discount' FROM discount_transactions_iptraffic_all_1233435600 WHERE account_id = 140 AND (discount_date BETWEEN '1232888220' AND '1235566679') AND t_class <> 0 GROUP BY t_class,base_cost,account_id order by account_id, t_class, base_cost)
нет смайла нужного как в квипе а если SQLом ))) эхххх как все грустно((
Сделать вложенным запросом и суммировать то, что будет на выходе, например так:
Код: Выделить всё
SELECT Q1.t_class, Q1.base_cost, SUM(Q1.bytes), SUM(Q1.discount)
FROM
((SELECT t_class, base_cost, bytes, discount FROM discount_transactions_iptraffic_all WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140)
UNION ALL
(SELECT t_class, base_cost, bytes, discount FROM discount_transactions_iptraffic_all_1233435600 WHERE discount_date>=1232888220 AND discount_date <=1235566679 AND account_id=140)) AS Q1
GROUP BY Q1.t_class, Q1.base_cost
ORDER BY Q1.t_class, Q1.base_cost
Последний раз редактировалось Ata-man Ср фев 18, 2009 15:21, всего редактировалось 1 раз.
спасибо!!! Буду в ебурге пивом угощаю )))