How to Multiply 2 fields - 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

How to Multiply 2 fields

Author

Topic

asyed01
Starting Member

13 Posts

Posted - 2009-09-18 : 12:47:55

Hello experts

I’m working on a complex query. I need to get data from 2 tables records and province. Province has a list of all the provinces with the cour_charge for all particular province. i.e. ONT 3.23, QEC 4.34 and so on. Records table has the details of all the customers & provinces. Now I need to find what is the total for all the provinces. I’m using case to accomplish it. So far I’ve able to do a count on different provinces. However my problem is I don’t know how to multiply this total with cour_charge.

For reference here is my code

select distinct br.scan_province,
'TOTAL is' = case
when (select count(scan_province) from
billable_records where scan_province = 'QEC')
then 'Quebec'
when (select count(scan_province) from
billable_records where scan_province = 'ONT')
then 'Ontario'
else 'Unknown'
end
from billable_records br
left join billing_province bp
on br.scan_province = bp.province_code

Thanks a lot in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-18 : 14:47:43

Is the "the total for all the provinces" just the count of the billing_province rows for a province multiplied by the cour_charge for that province?

if so then can't you simply take the sum of the cour_charge? If you want it by province then add a group by province?


select sum(bp.cour_charge) 
from billable_records br
inner join billing_province bp
       on bp.province_code = br.scan_province

or by province:


select br.scan_province
       ,sum(bp.cour_charge) 
from billable_records br
inner join billing_province bp
       on bp.province_code = br.scan_province
group by br.scan_province
       ,cour_charge

Be One with the Optimizer
TG

asyed01
Starting Member

13 Posts

Posted - 2009-09-18 : 15:40:29

TG thanks a lot for your input. Well are you totally rite. But all i'll say sometime we can't think straight. Your second suggestion worked for me. Full points to you.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources