August 26, 2005 at 2:20 pm
Here is a query I use to add up some number. The counts for lcount and scount total up to match total. How do I figure out (in SQL) the percentage of scount and lcount of total? I know I start with lcount/total*100 and scount/total*100 but all I ever get back is zeroes. My columns are all defined as nchar(10).
select facility,
Lcount = sum(case when form='L' then 1 end),
Scount = sum(case when form='S' then 1 end),
total = sum(1)
from allfacilities allf join facility fac on allf.facility = fac.facilityid
group by facility, facilityname
order by facility
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
August 26, 2005 at 2:26 pm
Well you could do that client side, but if yo must know ::
Select *, 1.0 * Lcount/Total as PCount, 1.0 * Scount / Total as SCount from (
select facility,
Lcount = sum(case when form='L' then 1 end),
Scount = sum(case when form='S' then 1 end),
total = sum(1)
from allfacilities allf join facility fac on allf.facility = fac.facilityid
group by facility, facilityname
order by facility
) dtDemo
August 26, 2005 at 2:38 pm
Interesting. The multiplication times 1.0 makes it display correctly. So I was heading the right way but it looks like I had conversion issues.
what is the dtdemo is that the name of the subselect?
I had to remove the order by - invalid in subqueries.
After 5000 posts I am sure you are tired of people telling you thank you, I appreciate it, you saved my job, I want to have your baby, etc.
Patrick Allmond
batteryfuel.com
For all of your laptop, camera, power tool and portable battery needs.
August 26, 2005 at 4:09 pm
Never getting tired.
dtDemo is the name of the derived table (dt). I think you can use Select top 100 percent in this case to make it work... or order in the final select. As you can see a derived table is a kind of sub select... or a view created on the fly which can be use to do other joins, calculations, etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply