How to Multiply a Single Row with a Number from Column in Sql

How to multiply a single row with a number from column in sql

You need to join to a numbers table!

CREATE TABLE dbo.numbers (
   number int NOT NULL
)

ALTER TABLE dbo.numbers
ADD
   CONSTRAINT pk_numbers PRIMARY KEY CLUSTERED (number)
     WITH FILLFACTOR = 100
GO

INSERT INTO dbo.numbers (number)
SELECT (a.number * 256) + b.number As number
FROM     (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As a
 CROSS
  JOIN (
        SELECT number
        FROM   master..spt_values
        WHERE  type = 'P'
        AND    number <= 255
       ) As b

Here's where I keep my latest script: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql

Once you have this in place you perform a simple join:

SELECT KHKVKBelegePositionen.BelPosId As OrderPositionId
     , KHKVKBelegePositionen.Artikelnummer As ProductId
     , KHKVKBelegePositionen.Bezeichung1 As ProductName
     , KHKVKBelegePositionen.Menge As Quantity
     , numbers.number As ElemendId
FROM   KHKVKBelegePositionen
 INNER
  JOIN dbo.numbers
    ON numbers.number BETWEEN 1 AND KHKVKBelegePositionen.Menge

Need to multiply rows based on Number column in SQL

A simple method uses a recursive subquery:

with cte as (
      select Rowstamp, DocNos, ProductID, SiteCode, Description, qty, 1 as num
      from t
      union all
      select Rowstamp, DocNos, ProductID, SiteCode, Description, qty, num + 1
      from cte
      where num <= qty
     )
select Rowstamp, DocNos, ProductID, SiteCode, Description, num as qty
from cte;

Multiply rows in single query

One method uses recursive CTEs:

with cte as (
      select t1.entity, t1.number
      from table1 t1
      union all
      select cte.entity, cte.number - 1
      from cte
      where cte.number > 0
     )
select entity
from cte;

Note: Using the default settings, this is limited to 100 rows per entity. You can use OPTION (MAXRECURSION 0) to get around this.

You can also solve this with a numbers table, but such a problem is a good introduction to recursive CTEs.

Multiply rows by column value

You cannot declare a variable in the middle of a plpgsql code. It is also not necessary to create another anonymous code block for the second loop. Try this:

do
$$ 
declare 
 f record;
 counter integer :=0;
begin 
    for f in select id, multiplier, content from public.source_tbl  loop
      while counter < f.multiplier loop
         insert into public.dest_tbl(multiplier,content)
         select f.multiplier, f.content;
         counter := counter +1;
       end loop;   
       counter := 0;
    end loop;
end;
$$ language plpgsql;

Demo: db<>fiddle

How to multiply all values in one column to make one number?

In absence of an aggregate "product" function in SQL, one method uses arithmetic: you can sum the logarithm of each value, then take the the exponential of the result.

select exp(sum(ln(price_rate))) as price_factor
from mytable

FOr this to work properly, all values of price_rate must be greater than 0.

Multiplication of column values based on conditional row grouping in SQL Server

In SQL Server, one option uses a lateral join:

select t.*, x.percent_val
from #tmp_data t
cross apply (
    select exp(sum(log(t1.val))) percent_val
    from #tmp_data t1
    where t1.visitor = t.visitor and t1.visit_time > dateadd(day, - 7, t.visit_time) and t1.visit_time <= t.visit_time
) x
where t.purchase = 1

The lateral join recovers the visits of the last 7 days for the same visitor. Then, we use arithmetics to compute the aggregate product of the value (this works as long as val is greater than 0).

Demo on DB Fiddle:

visitor | visit_id | visit_time              |  val | purchase | percent_val
------: | -------: | :---------------------- | ---: | :------- | ----------:
      1 |     1002 | 2020-01-02 11:00:00.000 | 0.97 | True     |      0.2231
      1 |     1003 | 2020-01-02 14:00:00.000 | 0.55 | True     |    0.122705
      2 |     2001 | 2020-01-01 10:00:00.000 | 0.11 | True     |        0.11
      2 |     2004 | 2020-01-11 14:00:00.000 | 0.38 | True     |    0.034656

---

If you want to handle 0 values as well, then you can change the select clause of the suquery:

select case when min(val) = 0 
    then 0 
    else exp(sum(log(case when val > 0 then t1.val end))) 
end percent_val

How to Multiply all values within a column with SQL like SUM()

Using a combination of ROUND, EXP, SUM and LOG

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!3/d43c8/2/0

Explanation

LOG returns the logarithm of col a ex. LOG([Col A]) which returns

0
0.6931471805599453
1.0986122886681098
1.3862943611198906

Then you use SUM to Add them all together SUM(LOG([Col A])) which returns

3.1780538303479453

Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453']))) which returns

23.999999999999993

Then this is finally rounded using ROUND ROUND(EXP(SUM(LOG('23.999999999999993'))),1) to get 24

---

Extra Answers

Simple resolution to:

An invalid floating point operation occurred.

When you have a 0 in your data

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
WHERE [Col A] != 0

If you only have 0 Then the above would give a result of NULL.

When you have negative numbers in your data set.

SELECT (ROUND(exp(SUM(log(CASE WHEN[Col A]<0 THEN [Col A]*-1 ELSE [Col A] END))),1)) * 
(CASE (SUM(CASE WHEN [Col A] < 0 THEN 1 ELSE 0 END) %2) WHEN 1 THEN -1 WHEN 0 THEN 1 END) AS [Col A Multi]
FROM yourtable

Example Input:

1
2
3
-4

Output:

Col A Multi
-24

SQL Fiddle: http://sqlfiddle.com/#!3/01ddc/3/0

Multiply every entry in column by a fixed number in SQL

It's really easy:

UPDATE table SET age=age*2

How to multiply value in table column?

UPDATE TABLE
SET WIDTH = WIDTH * 10

---

Related Topics