有个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;
他们怎么能拼在一起显示成一个数据集呢。
解决方案 »
- 急,急急~~~在线等~~~~~~~~~~~联合查询查询时索引失效
- left join得到的结果为null,怎么设置一个默认值?
- oracle中表的问题
- 导出数据库表空间和方案的时候,为什么我每次都把整个方案都导了??
- oracle 去掉重复值问题,急
- toad中怎样对数据进行修改?可以对blob字段进行修改吗?
- 救命啦!在线等!急关于ora-01652
- 问大家Oracle9i和Oracle10g是什么关系?后面的字母i和g分别表示什么 ?是什么单词的缩写?知者进来领分!
- 高手请指点ORACLE的会话期系统参数设置问题
- 神奇的oracle语句,谁能告诉我这是怎么回事???
- 高分求救oracle 8.1.6不能正常启动问题!!!
- 一个基本的问题,怎么恢复dmp文件啊?
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