Monday, March 26, 2012

Grouping for Sum

My query:

SELECT Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name], SUM(Ticket.Barrels) AS Expr1
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP BY Ticket.Barrels, Lease.[RRC Lease Number], Lease.[Lease Name], Lease.[Field Name], Lease.OperatorID, Lease.OilGasOther, Lease.District,
Operator.[Operator Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

I would like to produce a table as follows:

RRC Number - Lease Name - Field Name -Sum of Barrels

0001 - Lease1 - Field1 - 120

0002 - Lease1 - Field3 - 340

0002 - Lease2 - Field3 - 120

Instead I have some of the data on several rows:

0001 - Lease1 - Field1 - 70

0001 - Lease1 - Field1 - 50

0002 - Lease1 - Field3 - 40

0002 - Lease1 - Field3 - 300

and so on ....

What am I doing wrong?

You need to clean up both the select columns and group by columns. I can see the Ticket.Barrels coumn included in both select and group by which is the reason for what you got. Try another on with this column and other unnecessary columns.
.
SELECT Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name], SUM(Ticket.Barrels) AS 'Sum of Barrels'
FROM ((Ticket INNER JOIN
Lease ON Ticket.LeaseID = Lease.LeaseID) INNER JOIN
Operator ON Lease.OperatorID = Operator.OperatorID)
WHERE (Ticket.SWDNumber = ?) AND (Ticket.TicketDate BETWEEN ? AND ?)
GROUP

BY Lease.[RRC Lease Number], Lease.[Lease Name],

Lease.[Field Name]
HAVING (Lease.District = ?) AND (Lease.OilGasOther = ?)

No comments:

Post a Comment