有个test表
day(日期) class(类别) price(价格)
2008-9-1 1 50
2008-9-1 2 40
2008-9-2 1 30
2008-9-2 2 20需要按日期对price汇总求和,并且需要对该月内所有日期,所有类别的价格求和,要求最后显示的结果是:
日期求和 本月内所有值求和
2008-9-1 90 140
2008-9-2 50 140
请教这个怎么写,
前部分:select sum(price) from test group by day;
后面是:select sum(price) from test;
他们怎么能拼在一起显示成一个数据集呢。
day(日期) class(类别) price(价格)
2008-9-1 1 50
2008-9-1 2 40
2008-9-2 1 30
2008-9-2 2 20需要按日期对price汇总求和,并且需要对该月内所有日期,所有类别的价格求和,要求最后显示的结果是:
日期求和 本月内所有值求和
2008-9-1 90 140
2008-9-2 50 140
请教这个怎么写,
前部分:select sum(price) from test group by day;
后面是:select sum(price) from test;
他们怎么能拼在一起显示成一个数据集呢。
select a.days,a.day_amt,b.month_amt
from(
select trunc(day) days,sum(price) day_amt from test group by day)a
(select trunc(day,'mm') months,sum(price) month_amt from test group by trunc(day,'mm') )b
where trunc(a.days,'mm')=b.months
SELECT DAY, SUM(SAL), S
FROM (SELECT S.*, SUM(price) OVER() S FROM test S)
GROUP BY DAY, S;
SQL> SELECT DAYS,
2 SUM(PRICE)
3 FROM TABLE_NAME TT
4 GROUP BY ROLLUP(DAYS,TRUNC(DAYS,'YYYY'));DAYS SUM(PRICE)
----------- ----------
9/1/2008 90
9/1/2008 90
9/2/2008 50
9/2/2008 50
140SQL>
SQL> SELECT DISTINCT DAYS,
2 SUM(PRICE) OVER(PARTITION BY DAYS) "DAYS",
3 SUM(PRICE) OVER(PARTITION BY TRUNC(DAYS,'YYYY')) "YEARS"
4 FROM TABLE_NAME TT
5 ;DAYS DAYS YEARS
----------- ---------- ----------
9/1/2008 90 140
9/2/2008 50 140SQL>
(SELECT to_date('2008-9-1','yyyy-mm-dd') day, 1 class,50 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-1','yyyy-mm-dd') day, 2 class,40 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 1 class,30 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 2 class,20 price
FROM DUAL
)
select day,
sum(price) 日期求和,
max(jj) 本月内所有值求和
from
(
select t.*,
sum(price) over (partition by to_char(day,'yyyy-mm')) jj
from test t
)
group by day;
SELECT DAY, S, SS
FROM (SELECT SUM(PRICE) SS FROM TEST),
(SELECT DAY, SUM(PRICE) S FROM TEST GROUP BY DAY);
(SELECT to_date('2008-9-1','yyyy-mm-dd') day, 1 class,50 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-1','yyyy-mm-dd') day, 2 class,40 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 1 class,30 price
FROM DUAL
UNION ALL
SELECT to_date('2008-9-2','yyyy-mm-dd') day, 2 class,20 price
FROM DUAL
)
select day,
sum(price) 日期求和,
jj 本月内所有值求和
from
(
select t.*,
sum(price) over (partition by to_char(day,'yyyy-mm')) jj
from test t
)
group by day,jj;
from (select day,sum(price) sum_p from test group by day)a,
(select to_char(day,'yyyy-mm') mon,sum(price) sum_p
from test
group by to_char(day,'yyyy-mm'))b
where to_char(a.day,'yyyy-mm') = b.mon