Calculating percentage from 2 sql queries. How? Help!!

[Joe_Science]

Here are my 2 queries.  how do I calculate the percentage of both.

                <cfquery name="gettotals" datasource="#request.dsn#">
                    SELECT count (*) as total
                    FROM transactions
                    where product_number=4
                </cfquery>
               
                 <cfquery name="getrepurchases" datasource="#request.dsn#">
                    SELECT SUM(Duplicates.TotalCount) as repurchasesum FROM (SELECT email, COUNT(*)
                    TotalCount
                    From [dev].[dbo].[transactions]
                    where product_number=4
                    Group By email
                    HAVING COUNT(*) > 1) AS Duplicates
                  </cfquery>

Help Please...

[ilssac]

I am going to assume that you know that to calculate a percentage you use the formula (part/whole) * 100.

You are going to have to explain which piece of your data is the "part" and which peice is the "whole" and why you are having difficulty putting them together into the percentatge forumal.

[TLC-IT]

Depending upon exactly which SQL implementation you are talking to, on the server side, you can get a lot of firepower out of subqueries, which can be of both the correlated and non-correlated flavors.

Web pages such as this typical one discuss the concept well.

The essential idea here is that you construct a query that incorporates both of these queries.  You can usually construct queries that involve subqueries that, in turn, also involve subqueries themselves.

The good news is ... when you actually present such a query to your SQL engine, the engine's so-called "query optimizer" (which is effectively a language compiler...) deduces on-the-fly what is the best way to get the result you want, based on the query you have presented.  The EXPLAIN verb allows you to ask the SQL engine what that so-called "execution plan" would be, without actually executing it.

Although the SQL statement implies a certain execution-sequence, in fact the query-optimizer will do things in the most efficient strategy it can come up with.

[TLC-IT]

Addendum:

If you are able to, in your query-of-subqueries, say "GROUP BY product_number," you can generate a single result-set for every product-number all at once.  Or, you could use "WHERE product_number IN (list)."

As a rule of thumb, I find that it is usually preferable to ask the query-engine to do a lot of work, all at once, than to repeatedly ask it to execute a slightly-different query over and over.

Again, asking the engine to EXPLAIN things will help you choose what would be the best overall strategy.

When you are using subqueries and such, you may well find that there are "several different ways to say it," such that different ways of saying the same thing produce considerably different execution-plans.  Yes, it is somewhat of a black art.  (And my rudimentary command of it is but a shadow...)