sql中的sumProduct [英] sumProduct in sql

问题描述

我正在尝试在服务器上的表中实现 sumproduct(来自 excel).

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.

样本数据和预期结果

只提及重要的列

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:我需要一些语法方面的帮助.我正在尝试重写这部分:

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

作为

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

编辑 3:让它工作像这样:

case
when b like 'axr%' then
(sum(c*e)/100*3423)end as sumProduct

并在代码末尾

group by  --had an error without this
a,b,c,d,e 

我如何为每个日期执行此操作(假设日期是a"列或任何名称).如何将 over (partition by a) 合并到上面的代码中?

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?

想要类似的东西

case 
when b like 'axr%' then
(sum(c*e)/100*3423 over (partition by a))end as sumProduct

推荐答案

sum-product 的语法在 SQL 中非常简单:

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.

你想要一个窗口函数:

select t.*,
       sum(c*e) over (partition by a)
from #mytable1;

这篇关于sql中的sumProduct的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！