yet more tsql fun

[Bonegavel]

here is the lovely query i've built so far


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:



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:


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

[Bonegavel]

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


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.

[Bonegavel]

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 ?

[Bonegavel]

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


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.

[adubroff]

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.

[Bonegavel]

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.