divide a query by a query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

divide a query by a query

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 14:41:32

I wanted to know if it possiable to divide the sum of 1 query by the sum of another query.

Query 1 = 223749
SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, compliance.year1
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008';

Query 2 = 16650
SELECT compliance.company_id, Sum(compliance.surrenderedCERs)
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID
HAVING compliance.Company_ID = 1;

so I wanted to divide query 2 by query 1 to find the percentage used

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-05 : 15:07:43

[code]
select (select sum(ordinal_position) q1
from master.information_schema.columns
where table_name = 'spt_values')
/
nullif((select convert(money, sum(ordinal_position)) q2
from master.information_schema.columns),0.00)
[/code]

Be One with the Optimizer
TG

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 15:17:41

I didnt understand how to use that script

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-05 : 15:44:27

It is just an example of using one query to devide by another. You would simply need to replace your queries with the inner sub-queries in my example. btw, you can take my script and run it just to see that it in fact works.

Be One with the Optimizer
TG

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 16:21:08

so for my query it would be like this

select (SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, compliance.year1
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008')
/
nullif((SELECT compliance.company_id, Sum(compliance.surrenderedCERs)
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID
HAVING compliance.Company_ID = 1)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-05 : 17:28:10

you're just missing the ",0.00)" at the end of your second query. That correlates to the NULLIF( function. That is in there to avoid a Devide by Zero error.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources