Hi,

I have what appears to be a problem with the SUM() function, although it is probably something I am doing wrong!

I have two SQL statements:

Statement 1:
-------------------

SELECT
Load_Place_Name,
Vessel_Type,
DWT
FROM
Shipments
WHERE
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'

returns 5 Rows:
Agioi Theodoroi | Crude Oil Tanker | 156532
Agioi Theodoroi | Crude Oil Tanker | 104280
Agioi Theodoroi | Bulk/Oil              | 109798
Agioi Theodoroi | Crude Oil Tanker | 160000
Agioi Theodoroi | Crude Oil Tanker | 160000

The second statement:

SELECT
Load_Place_Name,
Vessel_Type,
SUM(DWT)
FROM
Shipments
GROUP BY
Load_Place_Name,
Vessel_Type
HAVING
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'

returns 2 rows:
Agioi Theodoroi | Bulk/Oil | 109798
Agioi Theodoroi | Crude Oil Tanker | 4587678

The problem is that the SUM(DWT) doesn't return what I am expecting, for the Bulk/Oil entry which has one record it is ok, but for the Crude Oil Tanker entry I was expecting 580812 not 4587678?

Is there a problem with the SUM() function or am I doing something incorrectly?

Thanks
Nigel

Nigel


Try it as

SELECT
Load_Place_Name,
Vessel_Type,
SUM(DWT)
FROM
Shipments
WHERE
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'
GROUP BY
Load_Place_Name,
Vessel_Type

I don't know why you moved from WHERE to HAVING but I can't see a reason for it and that's probably what's screwing up.

If that's not it take out the GROUP BY and SUM and see what rows are returned.


Roy Lambert

Roy Lambert wrote:

Nigel


Try it as

SELECT
Load_Place_Name,
Vessel_Type,
SUM(DWT)
FROM
Shipments
WHERE
Load_Dte > DATE '2012-08-01'
AND
Load_Place_Name = 'Agioi Theodoroi'
GROUP BY
Load_Place_Name,
Vessel_Type

I don't know why you moved from WHERE to HAVING but I can't see a reason for it and that's probably what's screwing up.

If that's not it take out the GROUP BY and SUM and see what rows are returned.


Roy Lambert


Thanks Roy,

That fixed it!

I have moved from a MS Access background and the SQL in that is a bit weird in it's construct, especially when summing and grouping, so I thought the HAVING was interchageable with the WHERE.
How wrong could I be!

Thanks again.
Nigel

Nigel


If you're ever in doubt Tim has some good documentation. If you can't find the answer in there read the SQL 2003 standard spec. You may not find the answer but you'll soon stop caring.

Roy Lambert