Good day

I’m working on a query with MSSQL that has the results of sales and comparative sales per date period based on the different products sold.

The result of the query looks like this

Product:qty sold:qty sold last year

123 10 100

456 50 10

i’m trying to add another column that would give me the percentage of the 2 last columns so in this example
it would give me 10 and 500

In my query I have this:

SELECT tbl.Product,
  ROUND(SUM(tbl.Sale), 2) AS Sale,
  ROUND(SUM(tbl.Sale_comp), 2) AS Sale_Comp

the tbl is created from my query

FROM (
 /** query sale**/
   select ... from ... where ... group by ...
UNION
 /** query sale_com **/
   select ... from ... where ... group by ...
) tbl

GROUP BY tbl.product

my tbl.sale and tbl.sale_comp are 2 SELECT queries joined by an UNION so that they get all the products sold from all the orders in the system and I have a GROUP BY tbl.product so I see only the product and the qty sold based on a variable of date.

I tried:

ROUND(SUM(tbl.Sale * 100 / tbl.Sale_comp) ,2) AS Perc
which gives me a Divide by zero error encountered even if there is data in both. if I remove the ‘/ tbl.Sales_Comp’ it gives me the good the tbl.sale * 100 amount

(tbl.sale * 100 / tbl.Sale_comp) as Perc
tells me that tbl.sale and tbl.sale_comp is not in GROUP BY
If a add the 2 tbl in the GROUP BY I have the divide by zero error encountered.

this line will work if I have numbers on both
ROUND(ROUND(SUM(tbl.sale * 100), 2) / ROUND(SUM(tbl.sales_comp), 2),2) AS Perc

if 1 of the column has a 0, I get the divide by 0 error. I tried with the ISNULL() function placed at a couple of places but always getting divide by 0 error encountered

Not sure what I can do to make the divide work as the sum for each subquery works perfectly just need it to divide the 2 columnsyour text