Subtracting 2 values from a query and sub-query using CROSS JOIN in SQL

Question

I have a question that I'm having trouble answering.

Find out what is the difference in number of invoices and total of invoiced products between May and June.

One way of doing it is to use sub-queries: one for June and the other one for May, and to subtract the results of the two queries. Since each of the two subqueries will return one row you can (should) use CROSS JOIN, which does not require the "on" clause since you join "all" the rows from one table (i.e. subquery) to all the rows from the other one. To find the month of a certain date, you can use MONTH function.

Here is the Erwin document

This is what I got so far. I have no idea how to use CROSS JOIN in this situation

select COUNT(*) TotalInv, SUM(ILP.ProductCount) TotalInvoicedProducts
from Invoice I, (select Count(distinct ProductId) ProductCount from InvoiceLine) AS ILP 
where MONTH(inv_date) = 5

select COUNT(*) TotalInv, SUM(ILP.ProductCount) TotalInvoicedProducts
from Invoice I, (select Count(distinct ProductId) ProductCount from InvoiceLine) AS ILP 
where MONTH(inv_date) = 6

If you guys can help that would be great.

Thanks

Answer 1

The problem statement suggests you use the following steps:

• Construct a query, with a single result row giving the values for June.
• Construct a query, with a single result row giving the values for May.
• Compare the results of the two queries.

The issue is that, in SQL, it's not super easy to do that third step. One way to do it is by doing a cross join, which yields a row containing all the values from both subqueries; it's then easy to use SELECT (b - a) ... to get the differences you're looking for. This isn't the only way to do the third step, but what you have definitely doesn't work.

Answer 2

can't you do something with subqueries? I haven't tested this, but something like the below should give you 4 columns, invoices and products for may and june.

select (

select 'stuff' a, count(*) as june_invoices, sum(products) as products from invoices 
where month = 'june' 
 ) june , (

select 'stuff' a, count(*) as may_invoices, sum(products) as products from invoices 
where month = 'may' 
) may

where june.a = may.a