① sql语句统计每天、每月、每年的销售总额
一、SQL语句统计每年的销售总额
select year(ordertime) 年，
sum(Total) 销售合计
from 订单表
group by year(ordertime)
二、SQL语句统计每月的销售总额
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime
三、SQL语句统计每日的销售总额
select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime),
day(ordertime)
(1)sql语句每季度统计扩展阅读
mysql查询每天、每周、每月的数据方法
一、查询每天的数据
SELECT
COUNT(1) AS countNumber,
DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime
FROM
testTable
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')
二、查询每周的数据
SELECT
COUNT(1) AS countNumber,
WEEK(createTime) as dateTime
FROM
testTable
GROUP BY WEEK(createTime)
三、查询每月的数据：
SELECT
COUNT(1) AS countNumber,
MONTH(createTime) as dateTime
FROM
testTable
GROUP BY MONTH(createTime)
② sql语句分别按日,按周,按月,按季统计金额
表:consume_record
字段:consume (money类型)
date (datetime类型)
请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元
--按日
selectsum(consume),day([date])fromconsume_recordwhereyear([date])='2006'groupbyday([date])
--按周quarter
selectsum(consume),datename(week,[date])fromconsume_recordwhereyear([date])='2006'groupbydatename(week,[date])
--按月
selectsum(consume),month([date])fromconsume_recordwhereyear([date])='2006'groupbymonth([date])
--按季
selectsum(consume),datename(quarter,[date])fromconsume_recordwhereyear([date])='2006'groupbydatename(quarter,[date])
当然，如果你的oracle报错：'month'函数报错，可以试试 to_char(b.PAYMENT_TIME,'yyyy-mm')
档
③ SQL 如何做季度数据统计
3.按季度分组
select to_char(exportDate,'yyyy-Q'),sum(amount) from table1 group by to_char(exportDate,'yyyy-Q')
order by to_char(exportDate,'yyyy-Q');
试试这个吧
④ sql--按照季度统计销售额 怎么写
俩方法
selectyear(订单.订购日期)年份,
sum(casewhenmonth(订单.订购日期)between1and3then订单明细.单价*订单明细.数量else0end)一季度销售金额,
sum(casewhenmonth(订单.订购日期)between4and6then订单明细.单价*订单明细.数量else0end)二季度销售金额,
sum(casewhenmonth(订单.订购日期)between7and9then订单明细.单价*订单明细.数量else0end)三季度销售金额,
sum(casewhenmonth(订单.订购日期)between10and12then订单明细.单价*订单明细.数量else0end)四季度销售金额
from订单,订单明细
where订单.订单ID=订单明细.订单IDandyear(订单.订购日期)between1996and1998
groupbyyear(订单.订购日期)
selectyear(订单.订购日期)年份,
casewhenmonth(订单.订购日期)between1and3then'一季度'
whenmonth(订单.订购日期)between4and6then'二季度'
whenmonth(订单.订购日期)between7and9then'三季度'
whenmonth(订单.订购日期)between10and12then'四季度'end季度,
sum(订单明细.单价*订单明细.数量)金额
from订单,订单明细
where订单.订单ID=订单明细.订单IDandyear(订单.订购日期)between1996and1998
groupbyyear(订单.订购日期),
casewhenmonth(订单.订购日期)between1and3then'一季度'
whenmonth(订单.订购日期)between4and6then'二季度'
whenmonth(订单.订购日期)between7and9then'三季度'
whenmonth(订单.订购日期)between10and12then'四季度'end
你看你要用哪个
⑤ sql server如何按季度分组统计所有的数据
和按月份组的原理是一样的吧！
按月分组
按季度分组和按月分组的区别应该就是时间段的区别吧！
selectcasewhenmonth(date)=1ormonth(date)=2
ormonth(date)=3then'一季度'
whenmonth(date)=4ormonth(date)=5
ormonth(date)=6then'2季度'
whenmonth(date)=7ormonth(date)=8
ormonth(date)=9then'3季度'
whenmonth(date)=10ormonth(date)=11
ormonth(date)=12then'4季度'
else''end,sum(数量)
fromtable
groupby
casewhenmonth(date)=1ormonth(date)=2
ormonth(date)=3then'一季度'
whenmonth(date)=4ormonth(date)=5
ormonth(date)=6then'2季度'
whenmonth(date)=7ormonth(date)=8
ormonth(date)=9then'3季度'
whenmonth(date)=10ormonth(date)=11
ormonth(date)=12then'4季度'
else''end
⑥ 用sql语句求本季度比上一个季度多多少天
--计算给定日期所在季度的天数
declare @date datetime;
set @date = getdate()
--本季度第一天与下季度第一天所差的天数
select datediff(day,dateadd(quarter,datediff(quarter,0,@date),0),dateadd(quarter,1+datediff(quarter,0,@date),0))
--借助变量简化
select @date = dateadd(quarter,datediff(quarter,0,@date),0) --本季度第一天
select datediff(day,@date,dateadd(quarter,1,@date))
go
算出本季及前一季各自的天数，再减一下，即可。
⑦ 我写了一条sql语句查询northwind数据库一年每个季度的销售情况，可是结果每年每季度的结果都是一样的
select Year as 年份
, 第一季度= sum(case Q when 0 then amount end)
, 第二季度= sum(case Q when 1 then amount end)
, 第三季度= sum(case Q when 2 then amount end)
, 第四季度= sum(case Q when 3 then amount end)
from (
select year(ShippedDate) as Y, month(ShippedDate) / 4 as Q,
UnitPrice*Quantity*(1-Discount) as amount
from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID
and year(ShippedDate) is not null
group by year(ShippedDate) , month(ShippedDate) / 4
) as a
group by Y
order by Y
⑧ sql季度汇总
我没有Oracle环境，这是用sql server做的，可以达到你的要求，这个问题处理的关键在于怎样通过年月来获得季度的汇总，所以要先把ny给分解成年和季度字段（case when 那部分，oracle也支持），剩下的就是简单的group by了。可以参考一下下面的代码。
select 部门编号,年,季度,sum(值)
from
(
SELECT
ORGCODE 部门编号
,SUBSTRING(ny,1,4) 年
,case when 0<SUBSTRING(ny,5,2) AND SUBSTRING(ny,5,2)<4 THEN 1
WHEN 3<SUBSTRING(ny,5,2)AND SUBSTRING(ny,5,2)<7 THEN 2
WHEN 6<SUBSTRING(ny,5,2)AND SUBSTRING(ny,5,2)<10 THEN 3
WHEN 9<SUBSTRING(ny,5,2)AND SUBSTRING(ny,5,2)<13 THEN 4
ELSE 0
END 季度
,JHRWFZ 值
FROM JXKH_YDH
) tmp
group by 部门编号,年,季度
order by 部门编号
⑨ 如何写SQL语句按周统计和按季度统计
--sqlserver2005语法统计按周,月,季,年。
--按日
--selectsum(price),day([date])fromtable_namewhereyear([date])=
'2006'groupbyday([date])
--按周quarter
selectsum(price),datename(week,price_time)fromble_namewhere
year(price_time)='2008'groupbydatename(week,price_time)
--按月
selectsum(price),month(price_time)fromble_namewhereyear(price_time)
='2008'groupbymonth(price_time)
--按季
selectsum(price),datename(quarter,price_time)fromble_namewhere
year(price_time)='2008'groupbydatename(quarter,price_time)
--按年
selectsum(price),year(price_time)fromble_namewhere
year(price_time)>='2008'groupbyyear(price_time)
⑩ sql季度汇总
不清楚 年月是按照什么格式写的
所以就说下思路
select ORGCODE,
case when month in(1,2,3) then '第一季度'
when month in(4,5,6) then '第二季度' end ji,
sum(TBJIAF) TBJIAF
where year=条件年份
group by ORGCODE,
case when month in(1,2,3) then '第一季度'
when month in(4,5,6) then '第二季度' end
-----------------------------------------------------
差不多这个格式