Results 1 to 12 of 12

Thread: [RESOLVED] DataSet SQL GROUP By statement not working

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2013
    Posts
    178

    Resolved [RESOLVED] DataSet SQL GROUP By statement not working

    I'm querying an SQL view into a DataSet inside VB.net.

    I've been trying to do a simple Group By in my DataSet query that returns the error:

    Column "'KitWIP.DUE_DATE is invalid in the selected list because it is not contained in either an aggregate function or the GROUP BY clause."

    Everything I've read about the SQL GROUP By says I can just group by one column after an aggregate function. What am I missing?

    Code:
    SELECT        DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue, 
                             PartsDue, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
    FROM            KitWIP
    GROUP BY WONO

  2. #2
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: DataSet SQL GROUP By statement not working

    Like it says, your columns must be in the group clause, or in an aggregate function to be selected. See these examples
    Code:
    --additional column in group clause
    SELECT        WONO, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
    FROM            KitWIP
    GROUP BY WONO, QuoteNo
    
    --additional column in aggregate function
    SELECT        WONO, Min(QuoteNo) as FirstQuote, MAX(PK_ProjSetup) AS LatestPK
    FROM            KitWIP
    GROUP BY WONO
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Jul 2013
    Posts
    178

    Re: DataSet SQL GROUP By statement not working

    Thank you for the examples, but the end result that I'm after is to just Group By WONO, I don't want to group by any other column and I have no function to perform on any other column.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataSet SQL GROUP By statement not working

    Quote Originally Posted by Fedaykin View Post
    Thank you for the examples, but the end result that I'm after is to just Group By WONO, I don't want to group by any other column and I have no function to perform on any other column.
    You can't. How would that make sense? How are you going to group together multiple records with the same WONO value if they have values in other columns that are different? The only way you could do that is if WONO was the only column you were projecting. Quite simply, every column in your SELECT clause that is not involved in an aggregate must be specified in the GROUP BY clause.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Jul 2013
    Posts
    178

    Re: DataSet SQL GROUP By statement not working

    I understand, however there is only one column that is different. It's the pk_ProjSetup column. I only want to return the row that has the MAX pk_ProjSetup value for any WONO.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataSet SQL GROUP By statement not working

    Quote Originally Posted by Fedaykin View Post
    I understand, however there is only one column that is different. It's the pk_ProjSetup column. I only want to return the row that has the MAX pk_ProjSetup value for any WONO.
    Then you're going to have to perform two queries. The first will get the MAX(PK_ProjSetup) grouped by WONO and then the second will get all columns where PK_ProjSetup and WONO have the values from the first result set.

    By the way, this thread has nothing to do with VB.NET. It's purely a SQL question and therefore belongs in the Database Development forum. I will ask the mods to move this one and please post database questions in that forum in future.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Jul 2013
    Posts
    178

    Re: DataSet SQL GROUP By statement not working

    That sounds correct, because moving the all of the column names to the Group By statement returned results, but did not eliminate the duplicates:

    Code:
    SELECT        DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue, 
                             PartsDue, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
    FROM            KitWIP
    GROUP BY DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue, 
                             PartsDue, QuoteNo
    As far as the location of this post, I disagree. I'm working inside Visual Studio, inside of a DataSet, programming in VB.net. SQL statements are common place when programming in any language supported by Visual Studio. Please leave the question where I posted it.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: DataSet SQL GROUP By statement not working

    Quote Originally Posted by Fedaykin View Post
    As far as the location of this post, I disagree. I'm working inside Visual Studio, inside of a DataSet, programming in VB.net. SQL statements are common place when programming in any language supported by Visual Studio. Please leave the question where I posted it.
    No, it belongs in Database Development. SQL is SQL and the fact that you're working in VS with VB.NET is irrelevant. You could be working in C# or PHP or SQL Server Management Studio and the SQL code would be exactly the same regardless. That's like asking a question about knitting on an automotive forum because you happen to be in a car while your doing your knitting. Almost everyone who posts in the Database Development forum is writing an application in a .NET language or VB6 but their questions are about SQL, which is specific to databases so they ask them in the forum dedicated to databases.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: DataSet SQL GROUP By statement not working

    Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: DataSet SQL GROUP By statement not working

    Fedaykin, JM is doing you a favout by asking for it to be moved (and Si is doing you one by moving it). You're going to get much better answers to your question in the DB forum than you are in the VB.Net one.

    The reason all columns in the select list must be part of an aggregate function or be included in the group by is because otherwise SQLServer would not be able to resolve a single value for them when more than one possible value was presented. Take the following set of Fruit data:-
    Code:
    FruitID FruitType  Colour
    1         Apple       Green
    2         Apple       Yellow
    3         Apple       Red
    4         Apple       Green
    5         Banana     Yellow
    If you were to write the following query:-
    Code:
    Select FruitType, Colour
    From Fruit
    Group by FruitType
    What would the result be? For a banana the result is obvious, it's banana, yellow because that's the only possible answer from that data set. But what about apples? What value should be returned as the colour? You only want one row for Apples (because you specified that you wanted to group by Fruit Type) and you haven't given the database a mechanism for deciding which colour to return. For the query to make sense you would either have to group by both Fruit type and Colour (in which case you would get all three colours returned for Apples each returned as a separate row) or you would have to put colour in an aggregate function, e.g. Reddest (in which case you would get Red, because that's the reddest).

    When you've got a load of rows where all the value are identical as you described the correct thing to do is either put them in an arbitrary aggregate function like Min or include them in the group by. An arbitrary aggregate fuction works because it will return the same value for all rows if they're all the same (e.g. the reddest of red, red and red is... well... red) so it can be resolved to a single value. Putting them all in the group by is probably better though because it's explicitely collapsing the rows.

    As for why your query isn't eliminating the duplicates, it is. That's what Group By does. Which means what you're seeing aren't duplicates. I suggest you look very closely at two rows that appear to be duplicates because somewhere in tere there is a deffierence.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jul 2013
    Posts
    178

    Re: DataSet SQL GROUP By statement not working

    Thank you for all the feedback. You are correct that there are several differences causing the repeated lines. This was always known, I just mistakenly thought I could filter these with a single database view or SELECT statement. I ended up using a DataTable and DataSet and a For Each Row to get the granularity needed. This may not be the most efficient method, but returns the single rows needed. You could do all this with a single DB open/close, I just separated for clarity.

    Code:
        Dim dtWO As New DataTable
        Dim dsFill As New DataSet
    
            '''''''''''''''''''''''''''
            'Fill a DataTable
            Dim sqConW As New SqlClient.SqlConnection("Server=MYSERVER\SQLSTANDARD;Database=MYDATABASE; Trusted_Connection=True;")
            Dim sqCmdW As New SqlClient.SqlCommand
            Dim SqlW As String
    
            sqCmdW.Connection = sqConW            'create the DB connection 
            SqlW = "SELECT DISTINCT WONO FROM KitWIP"  'This produces a list of one each Work Order numbers
            sqConW.Open()                        'open the connection
    
            Dim AdapterW As New SqlDataAdapter
    
            AdapterW.SelectCommand = New SqlCommand(SqlW, sqConW)
            AdapterW.Fill(dtWO) 'Fills the Dataset with the list of single Work Order numbers
            sqConW.Close() 'close the connection
            '''''''''''''''''''''''''''
            '''''''''''''''''''''''''''
    
            'Fill a Dataset
            Dim dbWONO As String
            Dim AdapterF As New SqlDataAdapter
    
            For Each Row As DataRow In dtWO.Rows
                dbWONO = Row.Item("WONO")
    
                'Fill a Dataset
                Dim sqConF As New SqlClient.SqlConnection("Server=MYSERVER\SQLSTANDARD;Database=MYDATABASE; Trusted_Connection=True;")
                Dim sqCmdF As New SqlClient.SqlCommand
                Dim SqlF As String
    
                sqCmdF.Connection = sqConF            'create the DB connection 
                SqlF = "SELECT TOP (1) PART_NO, REVISION As Rev, CUSTNO, WONO, DESCRIPT, BLDQTY, DUE_DATE, ORD_TYPE, SONO, FabDue, PartsDue, QuoteNo, QuotePartNo FROM KitWIP WHERE WONO = '" & dbWONO & "' ORDER BY PK_ProjSetup DESC"
    
                'The SELECT statement above picks the latest primary key from the database by sorting descending and then choosing the top 1 primary key.
                
                sqConF.Open()                        'open the connection
                AdapterF.SelectCommand = New SqlCommand(SqlF, sqConF)
                AdapterF.Fill(dsFill) 'Fills a Dataset with the sql command above
                sqConF.Close() 'close the connection
            Next
    
            '''''''''''''''''''''''''''
    
            'Populate the DataGridView with the dataset filled by the Adapter above
            Me.DataGridView1.DataSource = dsFill.Tables(0)
    Normally I feel a great sense of victory when achieving my goal.. this time I'm still just bitter about it. My amateur opinion is that SQL should have some more robust filtering capabilities. Hopefully this will assist someone else.
    Last edited by Fedaykin; Dec 15th, 2013 at 04:30 PM.

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: [RESOLVED] DataSet SQL GROUP By statement not working

    My amateur opinion is that SQL should have some more robust filtering capabilities.
    It does. The problem is that you still haven't fully understood the inter-relationship between the Group By statement, the select statement and aggregate functions. When collapsing rows you can't just expect sql server to throw conflicting values away, you have to tell it what to do with them. This is very deliberate, you don't want sql server making arbitrary decisions for you because that could result in unexpecetd (and often undesirable) outcomes. Don't be so quick to condemnn the tools, that's the sign of a bad workman.

    Here's how you could have achieved the same result with a single query:-
    Code:
    with cteTopPKs as
    (Select WoNo, Max(PK_ProjSetup) as PK
    From KitWIP
    Group by WoNo)
    SELECT PART_NO, REVISION As Rev, CUSTNO, KW.WONO, DESCRIPT, BLDQTY, DUE_DATE, ORD_TYPE, SONO, FabDue, PartsDue, QuoteNo, QuotePartNo 
    FROM KitWIP KW
    Join cteTopPKs TPK
       on KW.PK_ProjSetup = TPK.PK
    Order By PK_ProjSetup Desc
    I typed that straight in to the post so haven't syntax checked it. You might find an error or two's crept in so be a bit careful with it.

    The principle is that the common table expression returns all the Top (or Max) Primary keys. You then join to that result in your main select statement and it acts as a filter. The final Order By is probably redunandant. I included it so that you'd get the same same ordering as in your code but I get the impression your more interested in the filtering than the ordering.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width