I want to implement cross multiplication in SQL queries, can anyone suggest me how to do it.

For example consider the values below

Column 1       Column 2
  10              5
  3               6
  4               8

I want to display this output

Column 1            Column 2
  10                  10 
  3                  30(i.e 10*3)
  4                 120(i.2 30*4)

as so on.


You need to define the column that denotes the desired order of multiplication. I'm not sure if that's what column2 is supposed to be in your example data and have just used a column called id for that purpose below.

Assuming your RDBMS supports window functions then you can use the idea from here.

The below uses some non standard TSQL functions you will need to translate into the dialect of SQL that your implementation is actually using (if you are not in fact using SQL Server)

Online Demo

         WHEN Min(Abs(Column1)) 
                OVER ( 
                  ORDER BY id) = 0 THEN 0 
         ELSE CASE 
                WHEN Sum(Sign(CASE 
                                WHEN Column1 < 0 THEN 1 
                                ELSE 0 
                       OVER ( 
                         ORDER BY id) % 2 = 1 THEN -1 
                ELSE 1 
              END * Exp(Sum(Log(Abs(NULLIF(Column1, 0)))) 
                          OVER ( 
                            ORDER BY id)) 
FROM   YourTable 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.