Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Problem with SUM() |
Thu, Aug 28 2014 4:59 AM | Permanent Link |
Nigel Informa | 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 |
Thu, Aug 28 2014 5:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 28 2014 5:47 AM | Permanent Link |
Nigel Informa | 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 |
Thu, Aug 28 2014 7:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |