Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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  


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.


Your help is much appreciated.
Thanks
Saikrishna
Posted
Updated 29-Feb-16 2:39am

1 solution

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:

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


or maybe even isnull could help.

SQL
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


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:

SQL
SELECT Constituency, 
SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END)
FROM Capacity
WHERE Constituency = 'Adilabad'
group by Constituency 
 
Share this answer
 
Comments
Member 10376341 29-Feb-16 9:34am    
Hey! it worked.
Thank you Andy Lanng. Thanks for your time, really appreciate that.

Regards
Saiekrishna
Member 10376341 29-Feb-16 17:00pm    
Hello Andy,

Is there any way to execute the function without group by?
Since am using the query in C# and i suppose we cant pass the value to the Label.

Any help?
Andy Lanng 29-Feb-16 17:04pm    
Yes. As you are selecting a single Constituency you can leave it out from the select. Now that there are no non-aggregate fields left you don't need a group by:
SELECT
SUM(CASE WHEN [IT-ITES1] = 'Approved' THEN ISNULL([IT-ITES],0) ELSE 0 END)
FROM Capacity
WHERE Constituency = 'Adilabad'
Member 10376341 29-Feb-16 17:07pm    
That was quick reply. I owe you Andy, thank you very much.

Greatly appreciate your gesture.

Thanks again!
Andy Lanng 29-Feb-16 17:08pm    
np - it's 10pm now so will have to answer any more questions tomorrow ^_^

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900