Counting-Null values, multiple Where clauses

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting-Null values, multiple Where clauses

Author

Topic

nfcgiant
Starting Member

3 Posts

Posted - 2011-11-29 : 09:27:20

Hi! I'm new to SQL after spending many years working with MS Access. I am trying to reproduce some of my Access queries in SQL. Unfortunately I've run into a problem or five.

I am using SSRS 2005 to generate reports. What I am trying to do is get a count of the same field with multiple where clauses (I'm not sure I'm explaining this correctly...)

In Access I could do it by writing multiple queries, and then writing a query that combined the previous ones (adding in IIF statements to change null to zero). I can't figure out how to do it in SQL. From the research I've done, it looks like I need to use a nested query or a derived table with a left join?

Here's what I have right now:

SELECT HN,
(SELECT COUNT(PourO) AS Expr1
FROM dbo.vwPouringUnion AS T1
WHERE (DP = 1) AND (RFL = 1) AND (HN = dbo.vwPouringUnion.HN)) AS Removed,
(SELECT COUNT(PourO) AS Expr1
FROM dbo.vwPouringUnion AS T2
WHERE (HN = dbo.vwPouringUnion.HN)) AS Poured
FROM dbo.vwPouringUnion
WHERE (PT BETWEEN @start AND @end)
GROUP BY HN
ORDER BY HN DESC

I am trying to count number of wheels poured and number of wheels taken off line grouped by HN.

For whatever reason, this query times out the server. When I had the "PT between @start and @end" in the select statements, it didn't limit the search to those dates and instead returned the results of the whole table.

Any help is greatly appreciated.

nfcgiant
Starting Member

3 Posts

Posted - 2011-11-29 : 09:55:37

I have also tried this:

SELECT dbo.vwPouringUnion.HN, COUNT(PourO) AS Poured
FROM dbo.vwPouringUnion LEFT OUTER JOIN
(SELECT COUNT(PourO) AS Removed
FROM dbo.vwPouringUnion
WHERE (DP = 1) AND (RFL = 1) AND (HN = dbo.vwPouringUnion.HN)) AS Removed
ON
dbo.vwPouringUnion.HN = Removed.HN
WHERE (PT BETWEEN @start AND @end)
GROUP BY HN
ORDER BY HN DESC

But it gives me the error message: Invalid column name 'HN'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:20:40

[code]
SELECT HN,
COUNT(CASE WHEN DP = 1 AND RFL = 1 THEN PourO ELSE NULL END) AS Removed,
COUNT(PourO) AS Poured
FROM dbo.vwPouringUnion
WHERE (PT BETWEEN @start AND @end)
GROUP BY HN
ORDER BY HN DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nfcgiant
Starting Member

3 Posts

Posted - 2011-11-29 : 11:23:30

Thank you so much! That worked perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:26:19

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources