1

Hi I am trying to create a SQL query were I multiply the values of 1 row by the values in its column in the row beneath it. How can I do it? multiplying two columns is easy but i seem stuck on multiplying 2 rows.

Jacopo
  • 129
  • 2
  • 11
  • 1
    possible duplicate of [SQL Server Query to bring groupwise multiplication](http://stackoverflow.com/questions/3653586/sql-server-query-to-bring-groupwise-multiplication) – gbn Nov 22 '11 at 06:56
  • 1
    Show us your table defs please – Marco Nov 22 '11 at 06:57
  • this will help: http://stackoverflow.com/questions/8185873/multiply-rows-in-t-sql – talha2k Nov 22 '11 at 07:00
  • There is no such concept as "row beneath it" in any SQL dialect. SQL has no "order" – gbn Nov 22 '11 at 07:22

2 Answers2

4

That depends on your situation.

To simplyfy it... if your table looks like this...

CREATE TABLE `test` (
  `number` BIGINT(20) NOT NULL,
  PRIMARY KEY (`number`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

... and some data in it like this...

number
------
     2
     3
     4

...you can do something like this:

SELECT 
  (SELECT number 
  FROM test 
  WHERE number = 2) * 
  (SELECT number 
  FROM test 
  WHERE number = 3) AS "product" 
FROM test 
LIMIT 1;

You might aswell just JOIN the table onto itself.

Bjoern
  • 15,934
  • 4
  • 43
  • 48
2

join on same table

select t1.val*t2.val from tab t1, tab t2 where t1.id=5 and t2.id=6

or

select t1.val*t2.val from tab t1, tab t2 where t1.id=5 and t2.id=t1.id+1
Ivan Buttinoni
  • 4,110
  • 1
  • 24
  • 44