Group By??? SQL Server2K

When you group on a field like Part_Number, you need to be doing something to the other columns...like summing.  Is this what you want?

SELECT DISTINCT [MainWIP].Part_Number, sum([imMTownStock].Quantity) as SMFQuantity, sum([MainWIP].Quantity) As Quantity, sum(convert(decimal(10,2),cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(MainWIP.Cost as float) * CAST(MainWIP.Quantity as float))) AS NetChange, [MainWIP].Tag From MainWIP LEFT JOIN imMTownStock ON ([MainWIP].Part_Number = [imMTownStock].PartNumber AND [MainWIP].Quantity <> [imMTownStock].Quantity)
group by [MainWIP].Part_Number, [MainWIP].Tag

Ok, I get you. No actually I was more looking to Order By part_number and then have a break in between each group of records

like this:

part_number     field1         field2        field3
7381279217        3                5              5
7381279217        0                2              6
7381279217        3                7              1


4864384343        5               3               5
4864384343        5                5               9
4864384343        5                6              5
4864384343        5                5               0


4684646844         9               9                9



I thought I could do that with Group By...

You still need to use GROUP BY because you are using aggregate functions (i.e. SUM). Also use ORDER BY clause. Where exactly do you get the error?

SELECT DISTINCT [MainWIP].Part_Number, sum([imMTownStock].Quantity) as SMFQuantity,
    sum([MainWIP].Quantity) As Quantity,
    sum(convert(decimal(10,2),cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) -
    cast(MainWIP.Cost as float) * CAST(MainWIP.Quantity as float))) AS NetChange,
    [MainWIP].Tag
From MainWIP
LEFT JOIN imMTownStock
    ON ([MainWIP].Part_Number = [imMTownStock].PartNumber AND [MainWIP].Quantity <> 
         [imMTownStock].Quantity)
GROUP BY [MainWIP].Part_Number, [MainWIP].Tag
ORDER BY [MainWIP].Part_Number

No, group by is only used to summarize data.  SQL can be coming from a query tool or a Java program.  There is no way to know how data returned fom a query is going to be used...so they don't support that kind of formatting.  You can add an order by...and list your columns.  That will at least get the data into the order you want:

SELECT DISTINCT [MainWIP].Part_Number, [imMTownStock].Quantity as SMFQuantity, [MainWIP].Quantity, convert(decimal(10,2),cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(MainWIP.Cost as float) * CAST(MainWIP.Quantity as float)) AS NetChange, [MainWIP].Tag From MainWIP LEFT JOIN imMTownStock ON ([MainWIP].Part_Number = [imMTownStock].PartNumber AND [MainWIP].Quantity <> [imMTownStock].Quantity)
order by [MainWIP].Part_Number

So there's no way to Order By and have a break or blank record in between each set of whatever it is I order by???

If you are doing this from within a language that supports OLE DB, like Visual Basic, you might want to check out the "Shape" language in the online SQL documentation.  It's pretty powerful at marshalling tiers of related data.

it depends on your tool you are running the query in.  Are you in Query Analyzer?  I don't think there's a way to get these results in that tool.  MS provides Reporting Services to provide higher quality reports that include grouping/breaking like you want.

>>So there's no way to Order By and have a break or blank record in between each set of whatever it is I order by???<<
Not exactly a blank line, but you can get subtotals. So that it looks like this (watch EE butcher the format):

part_number            field1            field2                  field3
7381279217                  3                  5                  5
7381279217                  0                  2                  6
7381279217                  3                  7                  1
                              6                  14                  12                        
4864384343                  5                  3                  5
4864384343                  5                  5                  9
4864384343                  5                  6                  5
4864384343                  5                  5                  0
                              20                  19                  19
4684646844                  9                  9                  9
                              9                  9                  9
                              

lol @ EE butcher the format comment

Compouter??????????

This query is actually being done in VisualBasic 6 using a Data Grid

sorry...

COMPUTE

Not sure what it will do in a data grid. It can't hurt to try though.

I get:

The sum or average aggregate operation cannot take a varchar data type as an argument.

So are you saying that imMTownStock].Quantity is a varchar?

That would be strange.  Then sum one of the other numeric fields instead.

It is strange. Most of the fields i'm using are numbers but varchar datatype. i didn't crate the db and i cant change it.
i can't sum any numeric field because everything is varchar.

you can cast them first then add them. something like this:

...., SUM(CAST([imMTownStock].Quantity AS int)) as SMFQuantity, ...