Help on SUM

Hi,

I have two tables.

table1

ID | EmpName | Salary | LocationCode
1 | aaaaaa | 1000 | 001
2 | aaaaaa | 2000 | 002
3 | aaaaaa | 3000 | 003
4 | aaaaaa | 4000 | 004
5 | aaaaaa | 5000 | 005

table2

ID | Loc_Code | Region
1 | 001,002 | 100
2 | 003,004,005 | 200

Locations inturn grouped in to Regions as shown in table2

Now i need to find sum of salaries in each region.

I have tried in this way but failed.
SELECT sum(salary) FROM table1 WHERE LocationCode in (SELECT Loc_Code FROM table2 where Region=100)

Can you please tell solution how to find sum of salares based on region.

Does the "table2" table actually contain the string '001,002' and '003,004,005' for the "Loc_Code" column? If so, can this be changed?

|||Yes, table2 contains exactly same data. It cant be changed as application is already in production.
|||

This is not a good design. While join your query performance will be very poor. Change the comma separated values into Rows.

Anyhow here the query for your current design,

Code Snippet

Create Table #table1 (

[ID] int ,

[EmpName] Varchar(100) ,

[Salary] int ,

[LocationCode] Varchar(100)

);

Insert Into #table1 Values('1','aaaaaa','1000','001');

Insert Into #table1 Values('2','aaaaaa','2000','002');

Insert Into #table1 Values('3','aaaaaa','3000','003');

Insert Into #table1 Values('4','aaaaaa','4000','004');

Insert Into #table1 Values('5','aaaaaa','5000','005');

Create Table #table2 (

[ID] int ,

[Loc_Code] Varchar(100) ,

[Region] Varchar(100)

);

Insert Into #table2 Values('1','001,002','100');

Insert Into #table2 Values('2','003,004,005','200');

Select Region,Sum([Salary]) from #table1 A

Join #table2 B on ',' + [Loc_Code] + ',' Like '%,' + A.[LocationCode] + ',%'

Group By

Region

help on re-write w/o temp table

This works, but i'd like to know how to re-write it so that i don't use a
temp table.
select h.stkhstDate,
Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
u.unvmemunvID
into #t1
from stkhst h join unvmem u
on u.unvmemcsiid = h.stkhstcsisym
where h.stkhstDate = 20050502
group by h.stkhstdate, u.unvmemunvID
order by stkhstdate
update i
set i.idxhstRSBuy = t.Buy,
i.idxhstRSSell = t.Sell,
i.idxhstRSX = t.RSinX,
i.idxhstRSO = t.RSinO
from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate =
t.stkhstDate
drop table #t1
thanks
kesPlease post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:0050C1C7-0D0A-4F4B-9E90-E66938DFD2FE@.microsoft.com...
> This works, but i'd like to know how to re-write it so that i don't use a
> temp table.
> select h.stkhstDate,
> Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
> Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
> SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
> SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
> u.unvmemunvID
> into #t1
> from stkhst h join unvmem u
> on u.unvmemcsiid = h.stkhstcsisym
> where h.stkhstDate = 20050502
> group by h.stkhstdate, u.unvmemunvID
> order by stkhstdate
> update i
> set i.idxhstRSBuy = t.Buy,
> i.idxhstRSSell = t.Sell,
> i.idxhstRSX = t.RSinX,
> i.idxhstRSO = t.RSinO
> from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate
> =
> t.stkhstDate
> drop table #t1
> thanks
> kes|||Thank you for responding.
i'm looking for advice only on this issue. I put the update script up for
reference and should have been more specific or phreased my question
differently.
i have a query using a group by and i don't know how to turn it into a updat
e
directly. I guess can it be done and do you know of any update query
examples that have multi agragates?
thanks
kes
"AB - MVP" wrote:

> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:0050C1C7-0D0A-4F4B-9E90-E66938DFD2FE@.microsoft.com...
>
>|||Try This:
update idxhst set
i.idxhstRSBuy = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'B'),
i.idxhstRSSell = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'S'),
i.idxhstRSX = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'X'),
i.idxhstRSO = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'O')
Where idxhstDate = '20050502'
"Kurt Schroeder" wrote:

> This works, but i'd like to know how to re-write it so that i don't use a
> temp table.
> select h.stkhstDate,
> Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
> Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
> SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
> SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
> u.unvmemunvID
> into #t1
> from stkhst h join unvmem u
> on u.unvmemcsiid = h.stkhstcsisym
> where h.stkhstDate = 20050502
> group by h.stkhstdate, u.unvmemunvID
> order by stkhstdate
> update i
> set i.idxhstRSBuy = t.Buy,
> i.idxhstRSSell = t.Sell,
> i.idxhstRSX = t.RSinX,
> i.idxhstRSO = t.RSinO
> from #t1 t join idxhst i on i.idxhstidxid = t.unvmemunvid and i.idxhstDate
=
> t.stkhstDate
> drop table #t1
> thanks
> kes|||> i'm looking for advice only on this issue.
Did you read the link I posted?|||thanks,
i was afraid of that. there are times when just going low rent is the better
option.
my script takes less that 1 sec, but i just wanted to checkout the
alternative.
thanks
kes
"CBretana" wrote:
> Try This:
> update idxhst set
> i.idxhstRSBuy = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'B'),
> i.idxhstRSSell = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'S'),
> i.idxhstRSX = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'X'),
> i.idxhstRSO = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'O')
> Where idxhstDate = '20050502'
>
> "Kurt Schroeder" wrote:
>|||How long does the "Right" way take?
"Kurt Schroeder" wrote:
> thanks,
> i was afraid of that. there are times when just going low rent is the bett
er
> option.
> my script takes less that 1 sec, but i just wanted to checkout the
> alternative.
> thanks
> kes
> "CBretana" wrote:
>|||yes
"AB - MVP" wrote:

> Did you read the link I posted?
>
>|||Usingthe update you provided i modified it so that it would run (sub idxhst
for i.)
update idxhst set
idxhst.idxhstRSBuy = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'B'),
idxhst.idxhstRSSell = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSBS = 'S'),
idxhst.idxhstRSX = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'X'),
idxhst.idxhstRSO = (Select Count(*)
From stkhst h join unvmem u
On u.unvmemcsiid = h.stkhstcsisym
Where u.unvmemunvid = idxhst.idxhstidxid
And stkhstDate = '20050502'
And stkhstRSXO = 'O')
Where idxhst.idxhstDate = '20050502'
it takes on average 3 times as long to run. In my case the "Right" answer is
my origional query which takes less than a second on all runs.
thank you for responding.
kes
"CBretana" wrote:
> How long does the "Right" way take?
> "Kurt Schroeder" wrote:
>|||We had the same problem as well sometime back.
[ select h.stkhstDate,
Sum(Case When h.stkhstRSBS = 'B' Then 1 End) as Buy,
Sum(Case When h.stkhstRSBS = 'S' Then 1 End) as Sell,
SUM(Case When h.stkhstRSXO = 'X' Then 1 End) as RSinX,
SUM(Case When h.stkhstRSXO = 'O' Then 1 End) as RSinO,
u.unvmemunvID ]
If the above select just gets one record, then you might be better of in
storing the values in a Varibale as
opposed to storing it in a temp table and then updating them.
Gopi
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:6A640FE1-99F0-4FFD-A96C-13B17D2D5BC6@.microsoft.com...
> Usingthe update you provided i modified it so that it would run (sub
> idxhst
> for i.)
> update idxhst set
> idxhst.idxhstRSBuy = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'B'),
> idxhst.idxhstRSSell = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSBS = 'S'),
> idxhst.idxhstRSX = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'X'),
> idxhst.idxhstRSO = (Select Count(*)
> From stkhst h join unvmem u
> On u.unvmemcsiid = h.stkhstcsisym
> Where u.unvmemunvid = idxhst.idxhstidxid
> And stkhstDate = '20050502'
> And stkhstRSXO = 'O')
> Where idxhst.idxhstDate = '20050502'
> it takes on average 3 times as long to run. In my case the "Right" answer
> is
> my origional query which takes less than a second on all runs.
> thank you for responding.
> kes
> "CBretana" wrote:
>

Help on a select query

HI all,

SELECT @.PmtId = PmtId
FROM EmpStatus WHERE
PmtStatusDt = @.PmtStatusDt AND (PmtStatusCd= 'T' OR PmtStatusCd='X' )

SELECT PmtID, PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM Emp_Pmt_Balance WHERE PmtId = @.PmtId
GROUP BY PmtBalanceCd

But my problem here is this that:
My PmtID can be multiple records. example of the tables is as below

EmpStatus
-----

PmtId PmtStatusDt PmtStatusCd

1 12/11/03 T
2 12/11/03 X
3 12/11/03 T

Emp_Pmt_Balance
------

PmtId PmtBalanceCd EmpPmtBalanceAmt
1 560 12.00
1 562 13.00
2 560 24.00
2 562 28.00
3 560 34.00 3 562 28.00

The expected result:

PmtBalanceCd EmpPmtBalanceAmt
560 70.00
562 69 .00

Any help to get this tuff working is appreciated.If you want your results to literally be this:

PmtBalanceCd EmpPmtBalanceAmt
560 70.00
562 69 .00

Then just drop the PmtID from your query and you will get the desired result:

SELECT PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM Emp_Pmt_Balance WHERE PmtId = @.PmtId
GROUP BY PmtBalanceCd

Terri|||I tried that. It just displays the results for PmtId =3

PmtBalanceCd EmpPmtBalanceAmt
560 34.00
562 28.00

Now I am trying the Coalesce function. But it gives me this error
"Syntax error converting the varchar value '2, 3' to a column of data type int."

DECLARE @.PmtId AS Int

DECLARE @.PmtIdList varchar(100)

SELECT @.PmtIdList = COALESCE(@.PmtIdList + ', ', '') +
CAST(PmtId AS varchar(5))
FROM FCTrans.dbo.Emp_Pmt_Status
WHERE CAST(FLOOR(CAST(PmtStatusDt AS float)) AS datetime) = @.PmtStatusDt AND (PmtStatusCd= 'T' OR PmtStatusCd='X' )

SELECT PmtBalanceCd, SUM (EmpPmtBalanceAmt)
FROM FCTrans.dbo.Emp_Pmt_Balance WHERE PmtId = Cast(COALESCE(@.PmtIdList,CAST(PmtId As varchar(5))) As Int) And PmtBalancePeriodCd ='P' AND (PmtBalanceCd='560' OR PmtBalanceCd='565' OR PmtBalanceCd='570' OR PmtBalanceCd='575' OR PmtBalanceCd='580')
GROUP BY PmtBalanceCd
GO|||OK, I think I understand better what you are looking for. You should be able to do it in one statement, like this:

SELECT
 Balance.PmtBalanceCd,
 SUM (Balance.EmpPmtBalanceAmt)
FROM
 Emp_Pmt_Balance AS Balance
INNER JOIN
 Emp_Pmt_Status AS Status
 ON
 Status.PmtID = Balance.PmtID AND
 Status.PmtStatusDt = @.PmtStatusDt AND
 Status.PmtStatusCd IN ('T','X' ) AND
 Balance.PmtBalancePeriodCd ='P' AND
 Balance.PmtBalanceCd IN ('560','565','570','575','580')
GROUP BY
 Balance.PmtBalanceCd

I don't have your tables set up, so I haven't tested it. But it should be close.

Terri

Help needed on sum query

i have two table, one containing banking files header (a) information and a
second table containing the banking transactions (b) they are linked by a
LedgerKey field
The banking transaction my have a trans code of either 99 0r 17. Im trying
to get the sum of the header files where transaction trans code = 99, as i
need to knw what the client credit is. There can be several Header records,
but none of the transaction codes are mixed ie one file header will only
have transcode 99 or transcode 17
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from Table1 a
JOIN Table2 b
on a.LedgerKey = b.LedgerKey
where a.Licence = 123456
AND b.TransCode = '99'
a.TransValue varchar(11)
a.LedgerKey Varchar(12)
a.Licence Varchar(6)
b.LedgerKey varchar(12)
b.TransCode varchar (2)
in this instance there are 7 records in table b and im getting a SUM result
of 7 x a.TransValue instread if 1 X a.TransValue
Im a bit reluctant to add the ddl as it wil take a few hours to create
sample date, as for ovious reasons i can not post up live account details.Peter Newman wrote:
> i have two table, one containing banking files header (a)
> information and a second table containing the banking transactions
> (b) they are linked by a LedgerKey field
> The banking transaction my have a trans code of either 99 0r 17. Im
> trying to get the sum of the header files where transaction trans
> code = 99, as i need to knw what the client credit is. There can be
> several Header records, but none of the transaction codes are mixed
> ie one file header will only have transcode 99 or transcode 17
> Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
> from Table1 a
> JOIN Table2 b
> on a.LedgerKey = b.LedgerKey
> where a.Licence = 123456
> AND b.TransCode = '99'
> a.TransValue varchar(11)
> a.LedgerKey Varchar(12)
> a.Licence Varchar(6)
> b.LedgerKey varchar(12)
> b.TransCode varchar (2)
> in this instance there are 7 records in table b and im getting a SUM
> result of 7 x a.TransValue instread if 1 X a.TransValue
> Im a bit reluctant to add the ddl as it wil take a few hours to create
> sample date, as for ovious reasons i can not post up live account
> details.
A few hours? It took me five minutes to simulate your situation:
LedgerKey Varchar(12),
TransValue varchar(11),
Licence Varchar(6))
create table #trans (
LedgerKey varchar(12),
TransCode varchar (2))
insert into #header
select 'abc','1000.00','123456'
insert into #trans
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
go
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from #header a
WHERE EXISTS (Select * FROM #trans b
where a.LedgerKey = b.LedgerKey and
a.Licence = 123456
AND b.TransCode = '99')
drop table #header
drop table #trans
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Help needed on simple SUM query

I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
Mo
Use square brackets as name delimiters - not single quotes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mo" <Mehile.Orloff@.gmail.com> wrote in message
news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
Mo
|||As Tom noted you need to remove the single quotes. I see your grouping is
incorrect too. Try this:
SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
FROM QuoteItem
GROUP BY QuoteId, QuoteItemId
HAVING QuoteId = '250033'
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||On Apr 2, 10:33Xam, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use square brackets as name delimiters - not single quotes.
> --
> X XTom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON X Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "Mo" <Mehile.Orl...@.gmail.com> wrote in message
> news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
> I tried this query:
> SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
> FROM `QuoteItem`
> GROUP BY `QuoteId`
> HAVING (`QuoteId` LIKE "250033")
> to get the values of each QuoteItemId.
> There are 7 different items ion this quote, but this query is summing
> them ALL together.
> I want results like:
> 250033, 1, 50
> 250033, 2, 38
> 250033, 3, 75
> 250033, 4, 100
> etc...
> Please advise where I'm getting it wrong.
> I googled the topic and I must just have some killer blinders on.
> I did try a few other things, but no go.
> I know it's going to be simple, but I'm just not seeing it.
> Thanks-a-bunch,
> Mo
Thanks.
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.
Thanks.
-Mo
|||On Apr 2, 10:55Xam, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> As Tom noted you need to remove the single quotes. I see your grouping is
> incorrect too. Try this:
> SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
> FROM QuoteItem
> GROUP BY QuoteId, QuoteItemId
> HAVING QuoteId = '250033'
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks, I'll put your advise into operation.
Regards.
|||>>
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.[vbcol=seagreen]
I don't think that was the (only) problem.
:-)

Help needed on simple SUM query

I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
MoUse square brackets as name delimiters - not single quotes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mo" <Mehile.Orloff@.gmail.com> wrote in message
news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
I tried this query:
SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
FROM `QuoteItem`
GROUP BY `QuoteId`
HAVING (`QuoteId` LIKE "250033")
to get the values of each QuoteItemId.
There are 7 different items ion this quote, but this query is summing
them ALL together.
I want results like:
250033, 1, 50
250033, 2, 38
250033, 3, 75
250033, 4, 100
etc...
Please advise where I'm getting it wrong.
I googled the topic and I must just have some killer blinders on.
I did try a few other things, but no go.
I know it's going to be simple, but I'm just not seeing it.
Thanks-a-bunch,
Mo|||As Tom noted you need to remove the single quotes. I see your grouping is
incorrect too. Try this:
SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
FROM QuoteItem
GROUP BY QuoteId, QuoteItemId
HAVING QuoteId = '250033'
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Apr 2, 10:33=A0am, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Use square brackets as name delimiters - not single quotes.
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "Mo" <Mehile.Orl...@.gmail.com> wrote in message
> news:35683345-c1af-4eac-b8fb-58e7bd73205d@.e10g2000prf.googlegroups.com...
> I tried this query:
> SELECT `QuoteId`, `QuoteItemId`, SUM(QtyQuoted*Price)
> FROM `QuoteItem`
> GROUP BY `QuoteId`
> HAVING (`QuoteId` LIKE "250033")
> to get the values of each QuoteItemId.
> There are 7 different items ion this quote, but this query is summing
> them ALL together.
> I want results like:
> 250033, 1, 50
> 250033, 2, 38
> 250033, 3, 75
> 250033, 4, 100
> etc...
> Please advise where I'm getting it wrong.
> I googled the topic and I must just have some killer blinders on.
> I did try a few other things, but no go.
> I know it's going to be simple, but I'm just not seeing it.
> Thanks-a-bunch,
> Mo
Thanks.
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.
Thanks.
-Mo|||On Apr 2, 10:55=A0am, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> As Tom noted you need to remove the single quotes. I see your grouping is
> incorrect too. Try this:
> SELECT QuoteId, QuoteItemId, SUM(QtyQuoted * Price)
> FROM QuoteItem
> GROUP BY QuoteId, QuoteItemId
> HAVING QuoteId =3D '250033'
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks, I'll put your advise into operation.
Regards.|||>>
I've figured out how to do what I need by replacing the HAVING
statement with a WHERE statement between the FROM and GROUP BY.
I don't think that was the (only) problem.
:-)

Help needed in an Updation Query

Hai ,
Can any one of, out there help me in the following query.
I have two tables, one table has to get updated with sum of another tables
value. (Explanation below:)
Is it possible to use a single update statement, (i have done it with using
cursors)
Explanation:
Table A:
Col1 Col2 col3 col4
1 100 341 1.0
2 100 342 2.0
Table B:
Col1 Col2 col3 col4
1 100 341 0.1
2 100 341 0.2
3 100 341 0.3
4 100 342 0.4
5 100 342 0.5
6 100 342 0.6
Col2 of Table A PK, and Col2 of Table B id FK similarily
Col3 of Table A PK, and Col3 of Table B id FK
I need to updated Table A's Col4 column with the Sum of Col4(table B)
grouped by Col3 (table b) at one stretch.
If i am not clear in the above explanation , the query will be
Q1)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
Q2)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
when executed separately. I need these two queries in one single shot using
inner join(if possible) . I tried to do it, but unable to do it.
Thanks in advance,
V.Boomesshupdate A
set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
B.col3=A.col3)
-oj
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
> Hai ,
> Can any one of, out there help me in the following query.
> I have two tables, one table has to get updated with sum of another tables
> value. (Explanation below:)
> Is it possible to use a single update statement, (i have done it with
> using
> cursors)
> Explanation:
> Table A:
> Col1 Col2 col3 col4
> 1 100 341 1.0
> 2 100 342 2.0
>
> Table B:
> Col1 Col2 col3 col4
> 1 100 341 0.1
> 2 100 341 0.2
> 3 100 341 0.3
> 4 100 342 0.4
> 5 100 342 0.5
> 6 100 342 0.6
>
> Col2 of Table A PK, and Col2 of Table B id FK similarily
> Col3 of Table A PK, and Col3 of Table B id FK
> I need to updated Table A's Col4 column with the Sum of Col4(table B)
> grouped by Col3 (table b) at one stretch.
> If i am not clear in the above explanation , the query will be
> Q1)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
> Q2)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
> when executed separately. I need these two queries in one single shot
> using
> inner join(if possible) . I tried to do it, but unable to do it.
>
> Thanks in advance,
> V.Boomessh
>
>|||Hai,
Thanks that worked.
V.Boomessh
"oj" wrote:

> update A
> set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
> B.col3=A.col3)
>
> --
> -oj
>
> "Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
> news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
>
>