Sum(Column) issue
-
Why do I always have SQL trouble on fridays?
Anyway, I have this query, where I'm trying to sum(column) based on a GROUP BY. I can get it to work with a small query, but when I expand the query, it stops working.
Here is the small query:
SELECT sum(OOForcedAmount) AS SumForced,IONetworkName FROM worktask, workrequest, internalnetwork
WHERE OOlinkItemID=EEid
AND EEinternalNetworkID=IOid
AND OOForcedDate between '9/1/2005' AND '9/15/2005'
GROUP BY IONetworkName
And it works perfectly...grouping each sum(WTbudgetAmount) with the appropriate IOofficeName.
However, when I try to combine it with a larger query, it doesn't work.
select IURaysAmount AS Rays, AdminNetwork.IONetworkName AS AdminNetwork,"
YU.YUNetworkName AS ClientNetwork,"
ZX.ZXnameLast + ', ' + ZX.ZXnameFirst AS Client,"
OOForcedAmount AS Forced, "
Sum(OOForcedAmount) AS SumForced "
from workOzone EE, Ozonegenerator ZX, clientNetwork YU, worktask OO,"
internalNetwork AdminNetwork, workYUmpleted IU "
where EEstatus != 'MAGIC' "
AND OOlinkItemID = EEid "
AND EEinternalNetworkID = IOid "
AND AdminNetwork.IOid = EEinternalNetworkID "
AND EEOzoneGeneratorID = ZXid "
AND ZXclientNetworkID = YUid "
AND OOForcedDate between '9/1/2005' AND '9/15/2005' "
AND OO.OOid = IUlinkItemID "
AND IURaysAmount > '00:00:00' "
group by AdminNetwork.IONetworkName, YU.YUNetworkName, ZX.ZXnameLast + ', ' + ZX.ZXnameFirst, IU.IURaysAmount, OO.OOForcedAmount "
ORDER BY AdminNetwork, ClientNetwork, Client ASC"
Am I missing something? Can I not combine queries in this way?
Thank you
-
Just at first glance...it'd be better if you use the ansi compliant syntax of joins...also, you're using aliases for your tables but I don't see tablename.columnname...you have the whole thing as one string not separated by a "."...except maybe in OO.OOid and AdminNetwork.IONetworkName, YU.YUNetworkName..
specify your joins using...
from workOzone WO inner join Ozonegenerator OG on WO.ozonegeneratorID = OG.ID etc....
**ASCII stupid question, get a stupid ANSI !!!** -
At first glance it appears you're grouping by OO.OOForcedAmount and trying to sum it.
This would return one row for each different value of OO.OOForcedAmount, and the sum of rows that equal that value.
-
Paul...if I don't put OO.OOForcedAbount in the Group By clause, I get this error:
Column 'OO.OOForcedAbount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
That's why I put it in there. Is this a no no?
Thank you
-
sushila,
Thank you for your reply. My problem with the joins, is that I think I have too many tables to be able to use a join.
I'm drawing data from all these tables:
from workOzone EE, Ozonegenerator ZX, clientNetwork YU, worktask OO,
internalNetwork AdminNetwork, workYUmpleted IU
I don't think I can do an inner join if I have multiple tables in the FROM part of the query.
I thought joins could only be done if there is only one table in the from clause...of course I am probably wrong.
-
Yes, you'll get that error - and definitely a "no no". You're trying to bring back each individual value for that column, and then sum it... which is going to return what I described. What are you trying to achieve?
You can use the join syntax Sushila described with multiple tables. Say you have tables A, B and C and want to inner join A to both B and C:
FROM A INNER JOIN B ON A.col = B.col
INNER JOIN C ON A.col = B.col
You can see C is joining A from the ON clause.
-
Jurushia - You can join as many tables as you want (just as you have done)...for inner joins the syntax shouldn't matter between "where" and "join tbl on..." - it'd just be good practice to use the ansi compliant syntax...
if you post the ddls of your tables, some sample data from each and the results you expect to see, it'd be easier for someone to troubleshoot - maybe your join criteria is filtering out some rows...?!?!
I'm also a little curious about...AND IURaysAmount > '00:00:00'...what exactly does this comparison do...one seems to be money and the other seems to be time ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Paul,
I understand your join example would work, but I meant something like this:
FROM A
INNER JOIN B on A.id=B.id
INNER JOIN C on D.id=C.id
INNER JOIN E on F.id=E.id
etc...
thank you
-
Sushia,
AND IURaysAmount > '00:00:00'
the time it takes for a certain amount of infrared and ultraviolent rays to reach a lense...
don't ask me what that means, I'm just the graphic designer/photographer and now forced-to-be SQL girl around here!
-
OOPS, I meant to type Sushila! Sorry!
-
Paul, each item has a force(OOForcedAmount) and each force is in a group. I wanted to display the individual force amounts, as well as the sum of all force amounts in each group.
-
wow, I'm a rookie now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply