July 5, 2008 at 9:40 am
Still a newbie to sql so this question may seem simple but I have no way to actually write the command.
I need the command to select those members whose number of days absent ("A") are greater than 5 days.
The Sample Attendance table is below, where each day their status is written:
Date MemID Status
1/01/01 0001 A
1/01/01 0002 A
1/01/01 0003 P
1/02/01 0001 P
1/02/01 0002 P
1/02/01 0003 A
....and so on and so on.
What I need the command to do is only return those MemID whose total number of Status "A" is greater than a specific number (5 for example). I'm not sure how to write this. Can someone help?
Thanks.
July 5, 2008 at 11:20 am
Mark,
Since you want results based on an aggregate function look at using the HAVING clause in a SELECT statement.
Try building a statement using that. If it doesn't work out just post what you've worked on.
July 5, 2008 at 4:01 pm
After looking around on the net and taking your advice, I did use the HAVING clause. It worked. However, I noticed that aggregate functions are rather slow to return results. Are there any other techniques to get the same results much faster.
Any ideas?
July 5, 2008 at 4:45 pm
Slow compared to what? How many rows are in your table?
Two things you might want to start with. Look at the query execution plan. That will tell you what type of operations are taking place when you run it. Second, you can have SQL give you some recommendations by using DTA. In SSMS select your query, right-click and choose Analyze Query in Database Engine Tuning Advisor.
July 5, 2008 at 9:29 pm
I am not sure what your query looks like, but based on the sample data the query should be pretty efficient. The SUM(1) will return an incremented value for each row that was aggregated in the group by.
Select MemID, sum(1) AbsentCount
FROM Tbl
WHERE Status = 'A'
GROUP BY MemID
Having SUM(1) > 5
July 7, 2008 at 1:19 pm
Ken,
I suppose there are always multiple ways to skin a cat, but I wonder what advantage you see in using "Sum(1)" rather than "Count(*)" in your query. Is there any performance benefit?
July 7, 2008 at 1:35 pm
...I wonder what advantage you see in using "Sum(1)" rather than "Count(*)" in your query. Is there any performance benefit?
I just ran
declare @date datetime
declare @date2 datetime
set @date = getdate()
select sum(1)
from raw_ic_Roster
group by personid
having sum(1) > 10
set @date2 = getdate()
print datediff(ms, @date, @date2)
and
declare @date datetime
declare @date2 datetime
set @date = getdate()
select count(1)
from raw_ic_Roster
group by personid
having count(1) > 10
set @date2 = getdate()
print datediff(ms, @date, @date2)
with ~505,000 rows and ~15,000 distinct personids.
count takes ~233-250ms and sum takes ~280-299ms
it's possible that internally, count does and inc command vs an add command, which the cpu may handle a bit better
July 7, 2008 at 1:36 pm
Hi,
1. You are looking for something which is based on grouping of Multiple records, so in this case you have to use HAVING,GROUP BY with out this there is no way you can achieve.
2. When you say, how are you saying it is slow? are you comparing this query performance with any other query? what is the other query?
3. How many reocrds you have in your table? what is the table structure? is it partitioned? how many indexes you have.
Please give us the information to guide you in better way.
Thanks -- Vj
July 7, 2008 at 1:43 pm
john.arnott (7/7/2008)
Ken,I suppose there are always multiple ways to skin a cat, but I wonder what advantage you see in using "Sum(1)" rather than "Count(*)" in your query. Is there any performance benefit?
I've never really thought about it much. Two reasons I guess.
1. Constatnly hearing never use *
2. It makes more logical sense to me because I am actually counting the number of row that were aggregated.
Mainly just preference.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply