Front Office Football Central - yet more tsql fun

Front Office Football Central (https://forums.operationsports.com/fofc//index.php)

- FOFC Archive (https://forums.operationsports.com/fofc//forumdisplay.php?f=27)

- - yet more tsql fun (https://forums.operationsports.com/fofc//showthread.php?t=68225)

yet more tsql fun

here is the lovely query i've built so far

Quote:

SELECT o.nOrderID, o.dOrderPlaced, v.sVendor, li.nItemID, i.sItemNumber, i.sItemDesc, li.nQty
FROM t_Order o INNER JOIN
t_Vendor v ON o.nVendorID = v.nVendorID INNER JOIN
t_Lineitem li ON o.nOrderID = li.nOrderID INNER JOIN
t_Item i ON li.nItemID = i.nItemID
WHERE (o.nVendorID = 1001) AND (o.dOrderPlaced > '08/31/2007 23:59:59 PM') AND (o.dOrderPlaced <= '09/30/2008 23:59:59 PM')
ORDER BY o.nOrderID DESC

This show me all the data I want. However, I want to sum all the same li.nitemid together. I know I have to utilize group by, but I become retarded when it comes to group by.

i'm getting closer, I figured out I couldn't keep in the norderid or the date since that wouldn't be able to group by that.

I know have:

Quote:

SELECT li.nItemID, i.sItemNumber, i.sItemDesc, li.nQty AS total
FROM t_Order o INNER JOIN
t_Vendor v ON o.nVendorID = v.nVendorID INNER JOIN
t_Lineitem li ON o.nOrderID = li.nOrderID INNER JOIN
t_Item i ON li.nItemID = i.nItemID
WHERE (o.nVendorID = 1001) AND (o.dOrderPlaced > '08/31/2007 23:59:59 PM') AND (o.dOrderPlaced <= '09/30/2008 23:59:59 PM')

I want to do this:

Quote:

SELECT li.nItemID, i.sItemNumber, i.sItemDesc, SUM(li.nQty) AS Expr1
FROM t_Order o INNER JOIN
t_Vendor v ON o.nVendorID = v.nVendorID INNER JOIN
t_Lineitem li ON o.nOrderID = li.nOrderID INNER JOIN
t_Item i ON li.nItemID = i.nItemID
WHERE (o.nVendorID = 1001) AND (o.dOrderPlaced > '08/31/2007 23:59:59 PM') AND (o.dOrderPlaced <= '09/30/2008 23:59:59 PM')
GROUP BY li.nItemID

but I get:

error: column i.sItemNumber is invalid because it is not contained in either an aggregate function or group by clause.

what's screwing me up is that I put everything into separate tables but now they are a pain to query :D

oops, I think I answered my own by removing the offending selects

Quote:

SELECT li.nItemID, SUM(li.nQty) AS Expr1
FROM t_Order o INNER JOIN
t_Vendor v ON o.nVendorID = v.nVendorID INNER JOIN
t_Lineitem li ON o.nOrderID = li.nOrderID INNER JOIN
t_Item i ON li.nItemID = i.nItemID
WHERE (o.nVendorID = 1001) AND (o.dOrderPlaced > '08/31/2007 23:59:59 PM') AND (o.dOrderPlaced <= '09/30/2008 23:59:59 PM')
GROUP BY li.nItemID
ORDER BY Expr1 DESC

Anyone have an easy way to think about sum and grouping... always fricks me up.

One thing, however, is I want to bring the item description over (so they can see what the item is - item number lone doesn't tell much) and I can't becaus of the group by.

Any way to keep in the i.sItemDesc ?

fucking moron... I just added it to the end

Quote:

SELECT li.nItemID, SUM(li.nQty) AS Expr1, i.sItemDesc
FROM t_Order o INNER JOIN
t_Vendor v ON o.nVendorID = v.nVendorID INNER JOIN
t_Lineitem li ON o.nOrderID = li.nOrderID INNER JOIN
t_Item i ON li.nItemID = i.nItemID
WHERE (o.nVendorID = 1001) AND (o.dOrderPlaced > '08/31/2007 23:59:59 PM') AND (o.dOrderPlaced <= '09/30/2008 23:59:59 PM')
GROUP BY li.nItemID, i.sItemDesc
ORDER BY Expr1 DESC

and it seems to be there now. I just needed to make a sql-ass out of myself to the world so I could figure it out.

Quote:

Originally Posted by Bonegavel (Post 1856555)

fucking moron... I just added it to the end

and it seems to be there now. I just needed to make a sql-ass out of myself to the world so I could figure it out.

The simple rule for summing/grouping is that any column which is returned in the recordset which is not being summed, has to in the group by.

Quote:

Originally Posted by adubroff (Post 1856572)

The simple rule for summing/grouping is that any column which is returned in the recordset which is not being summed, has to in the group by.

Aaaaaaaah! Thanks. When I added i.sItemDesc, enterprise manager auto-added it to the group by and I didn't see that. Makes total sense and I should probably take a few classes on SQL. Problem is I only use it intensely a few times a year.