select percent from table with other rows

Question

i select last_name percent from families table with round function ..my query is :

SELECT     F.family_id, F.last_name
FROM 
 (SELECT family_id, father_name, last_name, economic_state, father_state,
 ROW_NUMBER() OVER (PARTITION BY last_name
 ORDER BY last_name, economic_state DESC) RowNo
FROM  FamiliesForCalculationShare
WHERE economic_state = 'a' OR economic_state = 'b') F INNER JOIN
(SELECT last_name, Count(1) AS FamCount FROM FamiliesForCalculationShare
GROUP BY last_name) GR ON F.last_name = GR.last_name AND 
f.rowno <= round(GR.FamCount * 40 / 100., 0)

the problem is i have one family that has one last name ..and when the percent be 40 % or 30% ... the round function make it 0.4 or 0.3 for this one family and don't select it .. but i need round function for other families and want to select this one family .. so i need if (family_lastname_count = 1 ) select it whatever percent is .

score 0 · Accepted Answer · edited May 23 '17 at 12:10

0

If I understand your question correctly, you want the case where GR.FamCount = 1 to be special and always to select this row, irrespective of the other 40% calculation. If so, you can change the JOIN Condition to use a CASE.

AND f.rowno <= 
  CASE WHEN GR.FamCount = 1 
     THEN f.rowno -- Condition, when compared with f.rowno, will always be true
     ELSE round(GR.FamCount * 40 / 100., 0)
  END

SqlFiddle Here

I've also moved the filter on the 2 derived tables out of the join condition and into a where clause - although this doesn't make a difference on inner joins, it often leads to trouble in outer joins.

edited May 23 '17 at 12:10

Community

1
1

answered Mar 06 '14 at 04:52

StuartLC

104,537
17
209
285

yes .. it is right thank you ver very much Stuart .. i try to make it without case and the count of families become huge number and i dont know why .. any way thank you – Hamonbatra Mar 06 '14 at 05:01

select percent from table with other rows

1 Answers1