在 SQL Server2008 中使用 Sumif
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14613443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Sumif in SQL Server2008
提问by Brice
I currently work as a Healthcare Analyst. I have been using Microsoft Access the past couple years for querying and generating reports. My company now wants me to learn, expand my SQL knowledge and use SQL Server2008 daily going forward.
我目前担任医疗保健分析师。过去几年我一直在使用 Microsoft Access 来查询和生成报告。我的公司现在希望我学习、扩展我的 SQL 知识并每天使用 SQL Server2008。
I have made some progress understanding the differences between Access and SQL, but they are still many things I'm unfamiliar with.
我在理解 Access 和 SQL 之间的差异方面取得了一些进展，但它们仍然有很多我不熟悉的地方。
I would like some insight on sub queries, and how to use SUMIF in SQL. Below I have pasted step (1) the first part of a SQL sub query I have put together. Step(2) is the SQL view from Microsoft Accessthat I want to translate into SQL, but some insight is needed on the proper syntax for a SUMIF statement.
我想了解子查询，以及如何在 SQL 中使用 SUMIF。下面我粘贴了第 (1) 步，这是我放在一起的 SQL 子查询的第一部分。Step(2) 是来自 Microsoft Access 的 SQL 视图，我想将其转换为 SQL，但需要对 SUMIF 语句的正确语法有所了解。
Any insight is greatly appreciated...
任何见解都非常感谢......
Step (1) SQL Server 2008:
步骤 (1) SQL Server 2008：
SELECT
[GROUPING_OF_CLINIC_ID].CLINIC_ID
, OUTPAT_ACCT_REC.CHARGE_CLASS
, [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
, [GROUPING_OF_CLINIC_ID].REGISTRATION_CD
, OUTPAT_REGISTER.PRIMARY_FINAN
, REPLACE(CONVERT(varchar(10), OUTPAT_ACCT_REC.ENTRY_DATE, 101), '/', '') AS ENTRY_DATE
, SUM(ENTRY_AMOUNT) AS ENTRY_AMOUNT
FROM
OUTPAT_REGISTER
LEFT JOIN
OUTPAT_BILL_REF
ON
(
OUTPAT_REGISTER.PATIENT_NUMBER = OUTPAT_BILL_REF.PATIENT_NUMBER
)
AND (OUTPAT_REGISTER.REGISTRATION_CD = OUTPAT_BILL_REF.REGISTRATION_CD)
LEFT JOIN
OUTPAT_ACCT_REC
ON
(
OUTPAT_BILL_REF.PATIENT_NUMBER = OUTPAT_ACCT_REC.PATIENT_NUMBER
)
AND (OUTPAT_BILL_REF.BILL_REFERENCE = OUTPAT_ACCT_REC.REFERENCE_NUM)
LEFT JOIN
(
SELECT
CLINIC_ID
, PATIENT_NUMBER
, REGISTRATION_CD
FROM
OP_VISIT_HISTRY
GROUP BY
CLINIC_ID
, PATIENT_NUMBER
, REGISTRATION_CD
--ORDER BY CLINIC_ID
) AS [GROUPING_OF_CLINIC_ID]
ON
(
OUTPAT_REGISTER.PATIENT_NUMBER = [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
)
AND (OUTPAT_REGISTER.REGISTRATION_CD = [GROUPING_OF_CLINIC_ID].REGISTRATION_CD)
GROUP BY
[GROUPING_OF_CLINIC_ID].CLINIC_ID
, OUTPAT_ACCT_REC.CHARGE_CLASS
, [GROUPING_OF_CLINIC_ID].PATIENT_NUMBER
, [GROUPING_OF_CLINIC_ID].REGISTRATION_CD
, OUTPAT_ACCT_REC.ENTRY_DATE
, OUTPAT_REGISTER.PRIMARY_FINAN
HAVING
OUTPAT_ACCT_REC.ENTRY_DATE > '12/31/2010'
Step(2)From Access in SQL View:
步骤（2）从 SQL 视图中的访问：
SELECT [Non Recurring Clinic Step1].CLINIC_ID, dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, [TABLE LAYOUT].ENTRY_TYPE,
Sum(IIf([entry_date] Between #12/1/2012# And #12/31/2012#,[sumofentry_amount],0)) AS [Dec'12], Sum(IIf([entry_date] Between #11/1/2012# And #11/30/2012#,[sumofentry_amount],0)) AS [Nov'12], Sum(IIf([entry_date] Between #10/1/2012# And #10/31/2012#,[sumofentry_amount],0)) AS [Oct'12], Sum(IIf([entry_date] Between #9/1/2012# And #9/30/2012#,[sumofentry_amount],0)) AS [Sept'12], Sum(IIf([entry_date] Between #8/1/2012# And #8/31/2012#,[sumofentry_amount],0)) AS [Aug'12], Sum(IIf([entry_date] Between #7/1/2012# And #7/31/2012#,[sumofentry_amount],0)) AS [Jul'12], Sum(IIf([entry_date] Between #6/1/2012# And #6/30/2012#,[sumofentry_amount],0)) AS [Jun'12], Sum(IIf([entry_date] Between #5/1/2012# And #5/31/2012#,[sumofentry_amount],0)) AS [May'12], Sum(IIf([entry_date] Between #4/1/2012# And #4/30/2012#,[sumofentry_amount],0)) AS [Apr'12], Sum(IIf([entry_date] Between #3/1/2012# And #3/31/2012#,[sumofentry_amount],0)) AS [Mar'12], Sum(IIf([entry_date] Between #2/1/2012# And #2/29/2012#,[sumofentry_amount],0)) AS [Feb'12], Sum(IIf([entry_date] Between #1/1/2012# And #1/31/2012#,[sumofentry_amount],0)) AS [Jan'12], Sum(IIf([entry_date] Between #12/1/2011# And #12/31/2011#,[sumofentry_amount],0)) AS [Dec'11], Sum(IIf([entry_date] Between #1/1/2012# And #12/31/2012#,[sumofentry_amount],0)) AS 2012YTD, Sum(IIf([entry_date] Between #1/1/2011# And #12/31/2011#,[sumofentry_amount],0)) AS 2011YTD
FROM [TABLE LAYOUT] INNER JOIN ([Non Recurring Clinic Step1] INNER JOIN ((dbo_OUTPAT_REGISTER INNER JOIN dbo_HR_FINAN_CLASS ON dbo_OUTPAT_REGISTER.PRIMARY_FINAN = dbo_HR_FINAN_CLASS.FINAN_CLASS_CD) INNER JOIN dbo_OUTPAT_AREA_CDS ON dbo_OUTPAT_REGISTER.REGISTER_AREA = dbo_OUTPAT_AREA_CDS.REGISTER_AREA) ON ([Non Recurring Clinic Step1].REGISTRATION_CD = dbo_OUTPAT_REGISTER.REGISTRATION_CD) AND ([Non Recurring Clinic Step1].PATIENT_NUMBER = dbo_OUTPAT_REGISTER.PATIENT_NUMBER)) ON [TABLE LAYOUT].ENTRY_CLASS = [Non Recurring Clinic Step1].CHARGE_CLASS
WHERE (((dbo_OUTPAT_AREA_CDS.REG_AREA_TYPE)<>"RE"))
GROUP BY [Non Recurring Clinic Step1].CLINIC_ID, dbo_HR_FINAN_CLASS.SUMMARY_CATGRY, [TABLE LAYOUT].ENTRY_TYPE
ORDER BY [Non Recurring Clinic Step1].CLINIC_ID;
回答by Taryn
SUM(IIF())is the equivalent of using an aggregate with a CASEexpression:
SUM(IIF())相当于使用带有CASE表达式的聚合：
sum(case
when [entry_date] >= '2012-12-01' and [entry_date] <= '2012-12-31'
then [sumofentry_amount]
else 0
end) AS [Dec'12]
Then you will just repeat this as needed for your other columns.
然后，您将根据需要为其他列重复此操作。
The CASEand the aggregate function are pivoting the data from the row values into columns. In SQL Server 2005+ you can implement the PIVOTfunction, but you can also use something similar to below:
的CASE和聚集函数被枢转从行值的数据成列。在 SQL Server 2005+ 中，您可以实现该PIVOT功能，但您也可以使用类似于以下内容的内容：
select CLINIC_ID,
SUMMARY_CATGRY,
ENTRY_TYPE,
SUM(case when EntryMonth = 12 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Dec12],
SUM(case when EntryMonth = 11 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Nov12],
SUM(case when EntryMonth = 10 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Oct12],
SUM(case when EntryMonth = 9 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Sep12],
SUM(case when EntryMonth = 8 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Aug12],
SUM(case when EntryMonth = 7 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jul12],
SUM(case when EntryMonth = 6 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jun12],
SUM(case when EntryMonth = 5 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [May12],
SUM(case when EntryMonth = 4 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Apr12],
SUM(case when EntryMonth = 3 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Mar12],
SUM(case when EntryMonth = 2 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Feb12],
SUM(case when EntryMonth = 1 and EntryYear = 2012 then [sumofentry_amount] else 0 end) [Jan12],
SUM(case when EntryYear = 2012 then [sumofentry_amount] else 0 end) [2012YTD],
SUM(case when EntryYear = 2011 then [sumofentry_amount] else 0 end) [2011YTD]
from
(
SELECT [Non Recurring Clinic Step1].CLINIC_ID,
dbo_HR_FINAN_CLASS.SUMMARY_CATGRY,
[TABLE LAYOUT].ENTRY_TYPE,
datepart(month,[entry_date]) EntryMonth,
datepart(year,[entry_date]) EntryYear,
[sumofentry_amount],
FROM <yourtables and joins go here>
WHERE dbo_OUTPAT_AREA_CDS.REG_AREA_TYPE)<>'RE'
and datepart(year,[entry_date]) in (2011, 2012)
) src
group by CLINIC_ID, SUMMARY_CATGRY, ENTRY_TYPE
回答by gh9
You dont need to use the IIf([entry_date] Between #12/1/2012# And #12/31/2012#,[sumofentry_amount],0).
您不需要使用 IIf([entry_date] 之间 #12/1/2012# 和 #12/31/2012#,[sumofentry_amount],0)。
put the entry_date between timeA and Timeb in your where clause and just sum the sumofentry_amount. for example
将 timeA 和 Timeb 之间的 entry_date 放在 where 子句中，然后将 sumofentry_amount 相加。例如
remove the entire sum(...........) statement and put another group by datepart(month,entry_date)
删除整个 sum(.........) 语句并按 datepart(month,entry_date) 放置另一个组
so your query will look like
所以你的查询看起来像
YOUR SELECT STATEMENT
YOUR FROM STATEMENT
GROUP BY DATEPART(MONTH,ENTRY_DATE)-- USING THIS WILL SPLIT IT OUT INTO THE MONTHS YOU NEED
CREATE TABLE foo(
amount int null
,targetDate datetime null
)
INSERT INTO FOO (amount,targetDate) values(14,'01/01/2012')
INSERT INTO FOO (amount,targetDate) values(16,'01/01/2012')
INSERT INTO FOO (amount,targetDate) values(30,'02/01/2012')
INSERT INTO FOO (amount,targetDate) values(15,'02/01/2012')
INSERT INTO FOO (amount,targetDate) values(80,'03/01/2012')
INSERT INTO FOO (amount,targetDate) values(80,'04/01/2012')
INSERT INTO FOO (amount,targetDate) values(6,'03/01/2012')
Select sum(amount)
from foo
group by DATEPART(month, targetDate)
results set
30 1
45 2
86 3
80 4
结果集
30 1
45 2
86 3
80 4
