Multiply record fields with same mainID.

Hi everybody,
What's the most efficient way to get the following result:
I have a number of records with a MainID and a Value fields.
eg:
SubID MainID Value
1 1 1
2 1 1
3 1 2
4 1 3
5 2 1
6 2 1
7 3 2
8 3 2
I need the product of the Value field for each MainID.
The result has to be like this:
MainID PrValue
1 6 (1*1*2*3)
2 1 (1*1)
3 4 (2*2)
TIA,
Martin.
select MainID, sum(value) as PrValue
from table
group by MainID
"martin" <kashaan007@.hotmail.com> wrote in message
news:Of%23nDPnRFHA.3144@.tk2msftngp13.phx.gbl...
> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>
|||Try,
use northwind
go
create table t (
SubID int,
MainID int,
Value int
)
go
insert into t values(1, 1, 1)
insert into t values(2, 1, 1)
insert into t values(3, 1, 2)
insert into t values(4, 1, 3)
insert into t values(5, 2, 1)
insert into t values(6, 2, 1)
insert into t values(7, 3, 2)
insert into t values(8, 3, 2)
go
select
MainID,
POWER(10, SUM(LOG10(Value)))
from
t
group by
MainID
drop table t
go
I took the idea from:
The T-SQL Banker
http://msdn.microsoft.com/library/de...-SQLBanker.asp
AMB
"martin" wrote:

> Hi everybody,
> What's the most efficient way to get the following result:
> I have a number of records with a MainID and a Value fields.
> eg:
> SubID MainID Value
> 1 1 1
> 2 1 1
> 3 1 2
> 4 1 3
> 5 2 1
> 6 2 1
> 7 3 2
> 8 3 2
> I need the product of the Value field for each MainID.
> The result has to be like this:
> MainID PrValue
> 1 6 (1*1*2*3)
> 2 1 (1*1)
> 3 4 (2*2)
> TIA,
> Martin.
>
>
|||Hi,AMB
It gives me a wrong output for the col1=2
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (1,3)
INSERT INTO #Test VALUES (2,4)
INSERT INTO #Test VALUES (2,2)
SELECT col1,POWER(10, SUM(LOG10(col2)))
FROM #Test GROUP BY col1
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/de...-SQLBanker.asp[vbcol=seagreen]
>
> AMB
> "martin" wrote:
|||Alejandro's solution is good for values greater than zero. Here's a
more general solution for all integers (this one adapted from Celko and
others):
SELECT mainid,
CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM T
GROUP BY mainid
David Portas
SQL Server MVP
|||Thanks a lot.
Works great.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> Try,
> use northwind
> go
> create table t (
> SubID int,
> MainID int,
> Value int
> )
> go
> insert into t values(1, 1, 1)
> insert into t values(2, 1, 1)
> insert into t values(3, 1, 2)
> insert into t values(4, 1, 3)
> insert into t values(5, 2, 1)
> insert into t values(6, 2, 1)
> insert into t values(7, 3, 2)
> insert into t values(8, 3, 2)
> go
> select
> MainID,
> POWER(10, SUM(LOG10(Value)))
> from
> t
> group by
> MainID
> drop table t
> go
> I took the idea from:
> The T-SQL Banker
>
http://msdn.microsoft.com/library/de...us/dnsqlmag02/
html/TheT-SQLBanker.asp[vbcol=seagreen]
>
> AMB
> "martin" wrote:
|||If we add +0.001 it works fine
SELECT col1,POWER(10, SUM(LOG10(col2))+0.001)
FROM #Test GROUP BY col1
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYVS5cnRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
>
http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
|||Cast 10 to float.
SELECT
col1,
cast(POWER(cast(10 as float), SUM(LOG10(col2))) as decimal(5))
FROM #Test GROUP BY col1
go
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
>
|||Good catch!!!
AMB
"Uri Dimant" wrote:

> Hi,AMB
> It gives me a wrong output for the col1=2
> CREATE TABLE #Test
> (
> col1 INT NOT NULL,
> col2 INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,10)
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (1,3)
> INSERT INTO #Test VALUES (2,4)
> INSERT INTO #Test VALUES (2,2)
>
> SELECT col1,POWER(10, SUM(LOG10(col2)))
> FROM #Test GROUP BY col1
>
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:2DA4CABB-2DBD-43CA-B0DE-995B8B644F14@.microsoft.com...
> http://msdn.microsoft.com/library/de...-SQLBanker.asp
>
>
|||David,
This is really a good one.
AMB
"David Portas" wrote:

> Alejandro's solution is good for values greater than zero. Here's a
> more general solution for all integers (this one adapted from Celko and
> others):
> SELECT mainid,
> CAST(ROUND(
> COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
> * SIGN(MIN(ABS(value)))
> * (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
> ,0) AS INTEGER) AS product
> FROM T
> GROUP BY mainid
> --
> David Portas
> SQL Server MVP
> --
>