September 30, 2005 at 2:27 am
Hello,
I need to right a query which will do the following:
If <Field> like '%1%' then
set @Lcount = Lcount + 1
If <Field> like '%2%' then
set @Lcount = Lcount + 2
If <Field> like '%3%' then
set @Lcount = Lcount + 3
Basically, I have a field with a string that contains amongst other things the numbers 1, 2, 3. I need to total these figures and then work out the percentage against the total number of records returned.
So if I have 2 records one with the <field> containing 1 and another record with <field> containing 2 the total will be 3.
I also then need to show this total and the percentage on the same line but in another field.
I presume I need to loop through the records to get the data, but I am not sure how to do any of this.
Any assistance is greatly appreiciated.
thanks
September 30, 2005 at 5:45 am
By percentage, do you mean the percentage of records that have 1, 2 or 3 in them? If so, this should work. If not, please post example data and required results.
SELECT sum(CASE
WHEN fld LIKE '%1%' THEN 1
WHEN fld LIKE '%2%' THEN 2
WHEN fld LIKE '%3%' THEN 3
ELSE 0
END) AS TotalSum,
sum(CASE
WHEN fld LIKE '%[1-3]%' THEN 1.0
ELSE 0
END)/COUNT(*)*100 AS TotalPercentage
FROM tbl
HTH
-- Edited for completeness and correctness
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2005 at 5:56 am
Looping is not a good idea but sometime, paticularly for one time deals, is needed. Here are two basic loops:
Cursor:
DECLARE MyCursor CURSOR FOR
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @pkClaim
WHILE (@@FETCH_STATUS -1)
BEGIN
-- put your logic here
FETCH NEXT FROM MyCursor INTO @pkClaim
END
Close MyCursor
Deallocate MyCursor
Counter loop:
DECLARE @a int
SET @a = 0
WHILE @a < 10
BEGIN
-- Do stuff here
END
--this will loop 10 times.
September 30, 2005 at 6:05 am
Sorry...I didn't test the cursor loop. Just did it freehand:
Declare @pkClaim int
DECLARE MyCursor CURSOR FOR SELECT pkClaim FROM Claims
Open MyCursor
FETCH NEXT FROM MyCursor INTO @pkClaim
WHILE (@@FETCH_STATUS -1)
BEGIN
Print 'looping'
FETCH NEXT FROM MyCursor INTO @pkClaim
END
Close MyCursor
Deallocate MyCursor
September 30, 2005 at 6:22 am
If you absolutely must use a cursor (which is completely unnecesary in this case, and in most cases where people use them) then al least dclare it with sensible options.
DECLARE CURSOR curUnnecessary FAST_FORWARD FOR SELECT....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2005 at 3:41 am
Thanks to all, I shall give it a go.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply