SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: GROUP BY Access

  1. #1
    SitePoint Addict -Ice-php's Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY Access

    Hey,

    I am used to using MySQL for most of my database work, but for a college project I have to use access for an Order System.

    I am having a problem with the GROUP BY clause. In MySQL you are able to group by one field only which is really what I need to do now but I cant figure it out.

    I am creating a query for the current months sales statistics. I want to group by the Order Id and calculate sums for the products (net, vat and total).

    I can only get the query to execute if I put all the fields (design view) to GROUP BY (except the summation ones).

    This is the SQL the query is producing:

    Code:
    SELECT Orders.[Order Id], Orders.Date, Customers.Company, [Order Line].Quantity, Shipping.Price, Sum(Products.Price*[Order Line].Quantity)+Shipping.Price AS Net, Sum((Products.Price*[Order Line].Quantity)*0.175)+(Shipping.Price*0.175) AS VAT, [Net]+[VAT] AS Total
    FROM Shipping 
    INNER JOIN (Products 
    INNER JOIN ((Customers 
    INNER JOIN Orders ON Customers.[Customer Id] = Orders.[Customer Id]) 
    INNER JOIN [Order Line] ON Orders.[Order Id] = [Order Line].[Order Id]) ON Products.[Product Id] = [Order Line].[Product Id]) ON Shipping.[Shipping Id] = Orders.[Shipping Id]
    GROUP BY Orders.[Order Id], Orders.Date, Customers.Company, [Order Line].Quantity, Shipping.Price
    HAVING (((Orders.Date)>="01/" & Format(Date(),"mm/yyyy") And (Orders.Date)<Format(DateAdd("m",1,Date()),"mm/yyyy")))
    ORDER BY Orders.Date;
    When I run this query, because everything is being grouped, it works fine until I have different quantities of different products being ordered.

    So really my question is, is there a way to group only by the Order Id so that all the records are combined into one order with the value summed up?

    If you need any more information about the query or my database structure please ask.

    Thanks in advance for your help!
    -Ice

  2. #2
    SitePoint Addict -Ice-php's Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    I have just realised how stupid I actually am. I don't need the Shipping or the Quantity to be shown at all. By Omitting them from being selected the query works as expected!
    -Ice

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the query may never work as expected until you write a correct GROUP BY

    according to the sql standard, a correct GROUP BY contains every non-aggregate column in the SELECT clause

    in other words, when you wrote this --
    I can only get the query to execute if I put all the fields (design view) to GROUP BY (except the summation ones).
    you were actually experiencing Access enforcing the standard

    the fact that mysql doesn't enforce the sql standard is doing an injustice to many people who never learn what the sql standard for GROUP BY is

    but alas, that's not the end of the story

    Access has its own flaws -- in particular, you cannot, according to the sql standard, reference a column alias in the SELECT clause, as you are doing here with [Net]+[VAT] AS Total

    mysql would give you an error on that

    so, what's a person to do?

    stick to the standard in all cases, chances are it will work!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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