在SQL查询中需要帮助 [英] Help needed in sql query

问题描述

Hello All，



我有一个包含公式的Excel文件，我将这些公式转换为SQL查询。大多数查询都是成功的，并得到我们的CodeProjeteer的帮助。



好​​吧，我只有一个查询根据特定的计算数据来自州的选区。在这里我得到答案，但结果中包含所有选区名称。



示例：

Hello All,

I have an Excel File with formulas, am converting those formulas into a SQL Query. Most of the query has been successful and helped by our fellow CodeProjeteer.

Well, am stuck with only one query where am calculating the data according to the particular Constituency from the State. Here i get the answer but with all the Constituency names in the result.

Example:

Adilabad	30
Alappuzha	NULL
Allhabad	NULL
Anakapalle	NULL

尽可能看到我的选区名称'Adilabad'有正确的数据，但我也得到了所有的选区名称。



我尝试了什么：

As you can see i get the Constituency name 'Adilabad' with correct data, but i also get all the constituency names too.

What I have tried:

SELECT Constituency, 
(SELECT  SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN [IT-ITES] ELSE 0 END) WHERE Constituency = 'Adilabad')
FROM Capacity
group by Constituency  

我有尝试从查询中删除group by子句但无法获得任何结果。



错误：

I have tried removing the group by clause from the query but couldn't get any result.

Error:

Msg 8120, Level 16, State 1, Line 1
Column 'Capacity.Constituency' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

哟非常感谢你的帮助。

谢谢

Saikrishna

Your help is much appreciated.
Thanks
Saikrishna

推荐答案

我想你可能正在寻找的是关键字 HAVING 。它有点像

I think what you may be looking for is the keyword HAVING. It's a bit like a

WHERE

子句但它适用于分组数据：

clause but it applies to the grouped data:

SELECT [columns]
FROM [tables]
WHERE [clauses]
GROUP BY [non-aggregates]
HAVING [aggregate clauses]

或者甚至isnull都可以提供帮助。

or maybe even isnull could help.

SELECT Constituency, 
SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END)
FROM Capacity
group by Constituency 
having SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END) > 0
and SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END) IS NOT NULL

这将消除任何没有价值的选区



另外，你可能只想消除任何其他选区是标准的Where子句：

This will eliminate any Constituency that doesn't have a value

Alternatively, You may just want to eliminate any other Constituency is the standard Where clause:

SELECT Constituency, 
SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END)
FROM Capacity
WHERE Constituency = 'Adilabad'
group by Constituency 

这篇关于在SQL查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！