① 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
-----------------------------------------------------
差不多這個格式