multiple resultsets: efficient

Showing posts with label efficient. Show all posts

Friday, March 23, 2012

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/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-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/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-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.
> >
> >
> >
> >|||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/default.asp?url=/library/en-us/dnsqlmag02/
html/TheT-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.
> >
> >
> >
> >|||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...
> > 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/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-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.
> > >
> > >
> > >
> > >
>|||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...
> > 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/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-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.
> > >
> > >
> > >
> > >
>
>|||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...
> > 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/default.asp?url=/library/en-us/dnsqlmag02/html/TheT-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.
> > >
> > >
> > >
> > >
>
>|||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
> --
>|||Use better the one posted by David Portas, and if you decide to use this one,
read the correction in my answer to Uri.
AMB
"martin" wrote:
> 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/default.asp?url=/library/en-us/dnsqlmag02/
> html/TheT-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.
> > >
> > >
> > >
> > >
>
>|||Ok thanks.
Martin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:210DC41A-BF9B-4211-9E16-995A554C0374@.microsoft.com...
> Use better the one posted by David Portas, and if you decide to use this
one,
> read the correction in my answer to Uri.
>
> AMB
> "martin" wrote:
> > 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/default.asp?url=/library/en-us/dnsqlmag02/
> > html/TheT-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.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Friday, March 23, 2012

Multiply record fields with same mainID.

Multiply record fields with same mainID.

Multiply record fields with same mainID.

multiple resultsets

Blog Archive

About Me