Problem with Having and Max

Hello,
I have the following query:
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
Group By
Code,
TestNo
Having
TestNo = Max(TestNo)
Order By
Code
What I want to achieve is summing only the weight for Max(TestNo) for each
code. But since I have to group by TestNo to use it in the Having clause it
is giving me the sum for all TestNo for each Code.
How can I achieve summing only the weight for Max(TestNo) for each code?
Thanks.
MichelTry,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Michel Hardy" wrote:

> Hello,
> I have the following query:
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> Group By
> Code,
> TestNo
> Having
> TestNo = Max(TestNo)
> Order By
> Code
> What I want to achieve is summing only the weight for Max(TestNo) for each
> code. But since I have to group by TestNo to use it in the Having clause i
t
> is giving me the sum for all TestNo for each Code.
> How can I achieve summing only the weight for Max(TestNo) for each code?
> Thanks.
> Michel|||Correction,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
and TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
> #tWidra.Code)
> Group By
> Code,
> TestNo
> Order By
> Code
>
> AMB
> "Michel Hardy" wrote:
>

Problem with Group By and Sum

I am trying to get a sum of the dollar amount for each term for each
customer. Unfortunately, the same value for sum is returned, so I thinkt
hat I have my query wrong. My query looks like this:
SELECT sales.name, sales.terms, t_order_term.order_term_description,
SUM(sales.prodamt) AS TheSum
FROM sales CROSS JOIN dbo.t_order_term
WHERE (NOT (dbo.sales.terms = N'12 , 24, 35, 39, 50, 57, 62'))
GROUP BY dbosales.name, dbo.t_order_term.order_term_description,
dbo.sales.terms
ORDER BY dbo.sales.name
My results look like this:
Customer1 30 988787.23
Customer1 60 988787.23
Customer2 30 78234.78
Customer2 60 78234.78
What am I doing wrong?
Thanks.
JoshuaDarn inner join problems...
I figured it out. Thanks.
Joshua
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uEhyPS8LFHA.3064@.TK2MSFTNGP12.phx.gbl...
> I am trying to get a sum of the dollar amount for each term for each
> customer. Unfortunately, the same value for sum is returned, so I thinkt
> hat I have my query wrong. My query looks like this:
> SELECT sales.name, sales.terms, t_order_term.order_term_description,
> SUM(sales.prodamt) AS TheSum
> FROM sales CROSS JOIN dbo.t_order_term
> WHERE (NOT (dbo.sales.terms = N'12 , 24, 35, 39, 50, 57, 62'))
> GROUP BY dbosales.name, dbo.t_order_term.order_term_description,
> dbo.sales.terms
> ORDER BY dbo.sales.name
> My results look like this:
> Customer1 30 988787.23
> Customer1 60 988787.23
> Customer2 30 78234.78
> Customer2 60 78234.78
> What am I doing wrong?
> Thanks.
> Joshua
>
>|||Perhaps something more like this?
SELECT S1.name, S1.terms, O1.order_term_descri=ADption,
SUM(S1.prod_amt) AS prod_amt_total
FROM Sales AS S1, OrderTerms AS O1
WHERE S1.terms NOT IN (12, 24, 35, 39, 50, 57, 62)
GROUP BY S1.name, S1.terms, O1.order_term_de=ADscription ;
Surely you do not have a CSV list in a column!

problem with GROUP BY and SUM

I'm having a problem with GROUP BY. There are multiple records for each registration.registrationid returned, and I need it to only return one record for each registration.registrationid and to add up all the TotalDollarsUsed for that registration.registrationid.

What ever I do I can't get it to SUM up the TotalDollarsUsed columns for each registrationid.

Does anyone have any ideas?

DECLARE@.SiteID int,
@.StartDate datetime,
@.EndDate datetime

SET @.SiteID = 2216
SET @.StartDate = '9/1/2003'
SET @.EndDate = '3/31/2004'

SELECT registration.registrationid, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI, [Session].Hours,
DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) AS TimeMinutes,
SUM(CASE [Session].Timebased WHEN 1 THEN DATEDIFF(minute, Attendance.TimeIn, Attendance.TimeOut) * (Rate / 60)
ELSE Hours * Rate
END) AS TotalDollarsUsed
FROM Attendance
INNER JOIN Registration ON Attendance.RegistrationID = Registration.RegistrationID
INNER JOIN [Session] ON Attendance.SessionID = [Session].SessionID
GROUP BY Registration.RegistrationID, Registration.LinkID, Registration.StudentID, Registration.LastName, Registration.FirstName, Registration.MI,
[Session].Timebased, [Session].Hours, [Session].Rate, Attendance.AttendanceDate, Attendance.TimeOut, Attendance.TimeIn, Registration.SiteID
HAVING (NOT (Session.Rate IS NULL))
AND (Attendance.AttendanceDate BETWEEN @.startdate AND @.enddate)
AND LinkID IN (SELECT LinkID FROM Registration WHERE withdrawdate IS NULL AND SiteID = @.SiteID)
AND SiteID = @.SiteID
ORDER BY Registration.LinkID

The results returned from this script are like this:

RegistrationID, TotalDollarsUsed
1001, 200
1001, 100
1001, 50
4005, 200
4005, 200Your SELECT statement is returning more than RegistrationID and TotalDollarsUsed columns. Your indicated result shows that for RegistrationID 1001 there are 3 records with unidentical value for a particular column that is not yet clear from your code. Eliminating such a column from the result may be an option. Post your full result to facilitate better resolution to the problem.