sumProduct in sql
Solution 1
The syntax for a sum-product is very simple in SQL:
select sum(c * e)
from #mytable1;
I am not quite sure how this applies to your query, which seems to have other logic in it.
EDIT:
You want a window function:
select t.*,
sum(c*e) over (partition by a)
from #mytable1;
Solution 2
Sincerely I haven't understood your piece of code. But assuming that you have two tables with values and a unique Id (for the join), then maybe my implementation can help you for inspiration:
-- create new DB or point to an existing one
use [test];
CREATE TABLE [dbo].[table1](
[id] [int] NOT NULL,
[value] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[table2](
[id] [int] NOT NULL,
[value] [int] NOT NULL
) ON [PRIMARY]
GO
insert into table1 values (1, 5), (2, 10);
insert into table2 values (1, 2), (2, 4);
select sum(P.products) as sumproduct from
(select (t1.value * t2.value) as products from table1 as t1 inner join table2 as t2 on t1.id = t2.id) as P
V2k
Updated on June 05, 2022Comments
-
V2k almost 2 years
I'm trying implementing sumproduct (from excel) in my table on the server.
select * into #myTable2 from #myTable1 select a, b, c, d, e, ( select (c * e)/100*3423) from #myTable1 t1 inner join #myTable t2 on t1.b = t2.b where b like 'axr%' ) as sumProduct from #myTable1
but this doesn't quite work. Can't spot the error, maybe i'm just tired or missing it.
edit: sample data and desired results
will mention only the important columns
c e b a sumProduct 2 4 axr1 2012.03.01 2*4 + 3*8 3 8 axr3 2012.03.01 2*4 + 3*8 7 5 axr23 2011.01.01 7*5 + 3*2 3 2 axr34 2011.01.01 7*5 + 3*2
EDIT2: I need some help with the syntax. I'm trying to rewrite this part:
select (c * e)/100*3423) from #myTable1 t1 inner join #myTable t2 on t1.b = t2.b where b like 'axr%' ) as sumProduct from #myTable1
as
case when t.b like 'axr%' then (sum(t.c * t.e) /100*3234) end as sumProduct from #myTable t
Can't get the syntax right, but should work like that
edit 3: got it to work like this:
case when b like 'axr%' then (sum(c*e)/100*3423)end as sumProduct
and at the end of the code
group by --had an error without this a,b,c,d,e
How could i do this for every date (let's say the date is the column 'a' or whatever name). How can I incorporate
over (partition by a)
in the code above?want something like
case when b like 'axr%' then (sum(c*e)/100*3423 over (partition by a))end as sumProduct