how to run aggregate function in expression - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


how to run aggregate function in expression

meherzad4umeherzad4u Posts: 3Member

tables in access
1.print the cars which are fast moving cars.

i have written this code but it shows message Cannot have aggregate function in expression

pl show me anyother way to solve this problem
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
da = New OleDbDataAdapter("select * from car where carno=(select carno from sales where sum(num)=(select max(sum(num)) from sales group by carno) group by carno)", cn)
DataGridView1.DataSource = ds.Tables(0)
End Sub [/code]


  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    I don't see anything wrong with the code.

    What line is it saying that information about? Is it saying that in the IDE as an error or warning? Is that message instead an Exception that is thrown at runtime when you click that button?

    If it is being thrown at run-time, I expect the error is in regards to your Select Statement being invalid.

    [code]select * from car where carno=(select carno from sales where sum(num)=(select max(sum(num)) from sales group by carno) group by carno)[/code]

    I have a feeling this SQL Statement is very wrong... I don't see where 'num' is queried or aliased in the statement, yet you call SUM() against it a bunch of time... You should test that SQL Query inside of the Database Management Tool (looks like your using an Access DB based on the object you chose, so run that query through Access). If the Query works there, it should work through code, so as a general rule of thumb, I test my SQL Queries through a SQL Management Client before I stick them into code.

    After analyzing your statement, I cannot find any logical reason why you have constructed it this way, without more knowledge about how your data is structured I cannot rewrite it for you.

    It should look more like this:
    SELECT CarNo FROM Car WHERE CarNo = (SELECT Sum(Num) as SumNum, CarNo From Sales WHERE SumNum = (SELECT CarNo, MAX(SUM(Num)) as MaxSum FROM Sales GROUP BY CarNo, MaxSum) GROUP BY CarNo, SumNum)

    SUM of a field is called in the Select portion of the statement, and no anywhere else (in your case you call it as a function inside of the Where Condition Declarations, which won't work.

    SUM'd fields need an Alias (AS SumNum aliases the field as SumNum and you can see that I reference that Field Alias later in the statement)

    GROUP BY clause requires ALL FIELDS IN THE SELECT DECLARATION to be grouped on, or the statement will fail. Therefore, I have to Group on SumNum and MaxSum in the two associated Select statements because they were "selected" as returned fields

    I am not even sure if the statement would work at all even if I did know your data structure... I suggest posting the Data schema of your database (what fields are in each table, which field is the primary key, and what fields are foreign keys for Relational Data purposes) and a word description of what data you are trying to Select from the database and we can go from there.

Sign In or Register to comment.