plz help me with the query - SQL Server Forums

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

plz help me with the query

Author

Topic

Dhanya_123
Starting Member

1 Post

Posted - 2011-07-18 : 13:09:22

i hav a table with a column named appr_backlog.it takes values y,n,a or nothing(not null).i hav to get the count of (n&y) in a single variable and a in another variable in a single query based on a particular date.I used the following query:-

select min(appr_backlog)as appr_backlog,min(distinct updatedon), case when min(appr_backlog)='y' then count(appr_backlog) end as count1,
case when min(appr_backlog)='n' then count(appr_backlog) end as count2,
case when min(appr_backlog)='a' then count(appr_backlog) end as count3
from aid.assurance
where updatedon between '2011-03-15' and '2011-07-09' and appr_backlog!=''
group by appr_backlog, updatedon
order by updatedon

it returns values.but my requirement is to obtain the count of n&y and a in single row for each date.can anyone plz suggest me a solution to solve the problem?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-18 : 18:07:09

You would need to remove the appr_backlog column from the group by list and put the when condition within the count function - something like this:


SELECT
	updatedon,
	COUNT (CASE WHEN appr_backlog = 'y' THEN 1 END ) AS count1,
	COUNT (CASE WHEN appr_backlog = 'n' THEN 1 END ) AS count2,
	COUNT (CASE WHEN appr_backlog = 'a' THEN 1 END ) AS count3
FROM
	aid.assurance
WHERE
	updatedon BETWEEN '2011-03-15' AND '2011-07-09'
	AND appr_backlog != ''
GROUP BY
	updatedon
ORDER BY
	updatedon

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-19 : 05:18:57

Use SUM(CASE WHEN appr_backlog = 'y' THEN 1 ELSE 0 END ) AS count1,
etc

to avoid unneccessary warning on nulls

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-19 : 05:21:16

Also try to use unambigious date format
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-19 : 07:17:14

quote:
Originally posted by madhivanan

Use SUM(CASE WHEN appr_backlog = 'y' THEN 1 ELSE 0 END ) AS count1,
etc

to avoid unneccessary warning on nulls

I have nothing to base this other than my (often wrong) intuition to base this on, but would count be more efficient than sum in this case?

With the SUM function, I am imagining the SQL engine opening the box, feeling inside the box to see how many there are, and then adding it up on a piece of paper. That seems harder than taking a quick peek in the box to see if there is anything in there and keeping the count.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-19 : 07:21:31

quote:
Originally posted by sunitabeck

quote:
Originally posted by madhivanan

Use SUM(CASE WHEN appr_backlog = 'y' THEN 1 ELSE 0 END ) AS count1,
etc

to avoid unneccessary warning on nulls

I have nothing to base this other than my (often wrong) intuition to base this on, but would count be more efficient than sum in this case?

With the SUM function, I am imagining the SQL engine opening the box, feeling inside the box to see how many there are, and then adding it up on a piece of paper. That seems harder than taking a quick peek in the box to see if there is anything in there and keeping the count.

I dont think there would be noticable difference.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources