Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Group By??? SQL Server2K

Here's my query:

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)

I want to group by MainWIP.Part_Number, but I keep getting errors. How would I properly do this?
0
Mike Miller
Asked:
Mike Miller
  • 7
  • 5
  • 3
  • +2
1 Solution
 
jrb1Commented:
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
0
 
Mike MillerSoftware EngineerAuthor Commented:
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...
0
 
PePiCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jrb1Commented:
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
0
 
Mike MillerSoftware EngineerAuthor Commented:
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???
0
 
Don SmithCommented:
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.

0
 
jrb1Commented:
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.
0
 
Anthony PerkinsCommented:
>>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
                              
0
 
PePiCommented:
lol @ EE butcher the format comment
0
 
jrb1Commented:
true...does this help?

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
Compouter sum([imMTownStock].Quantity)  by  [MainWIP].Part_Number
0
 
Mike MillerSoftware EngineerAuthor Commented:
Compouter??????????

This query is actually being done in VisualBasic 6 using a Data Grid
0
 
jrb1Commented:
sorry...

COMPUTE
0
 
jrb1Commented:
Not sure what it will do in a data grid. It can't hurt to try though.
0
 
Mike MillerSoftware EngineerAuthor Commented:
I get:

The sum or average aggregate operation cannot take a varchar data type as an argument.
0
 
Anthony PerkinsCommented:
So are you saying that imMTownStock].Quantity is a varchar?
0
 
jrb1Commented:
That would be strange.  Then sum one of the other numeric fields instead.
0
 
Mike MillerSoftware EngineerAuthor Commented:
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.

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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now