Results 1 to 6 of 6

Thread: Can you group within a query?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Aug 2000
    Location
    Fremont Ohio 43420
    Posts
    253
    I would like to see only one of each machineno and one total for that machineno. I'm not sure if you can do that. Is there a way to group by machineno?

    thanks jeffro

  2. #2
    Lively Member Dr_Evil's Avatar
    Join Date
    Mar 2000
    Location
    Columbus, OH
    Posts
    105
    Try this:

    SELECT COUNT(*), machineno
    FROM tablename
    GROUP BY machineno
    Dr_Evil
    Senior Programmer
    VS6 EE
    VS.NET EA

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Aug 2000
    Location
    Fremont Ohio 43420
    Posts
    253
    Where would I put the code you gave me within this sql statement?

    SELECT DISTINCTROW MachineInfo.Machineno, MachineInfo.Division, MachineMaintenance.RepairTime, MachineMaintenance.SetupTime, MachineMaintenance.PMTime, ([MachineMaintenance]![RepairTime])+([MachineMaintenance]![SetupTime])+([MachineMaintenance]![PMTime]) AS Total
    FROM MachineInfo INNER JOIN MachineMaintenance ON MachineInfo.Machineno = MachineMaintenance.Machineno;

    Thanks jeffro

  4. #4
    Lively Member Dr_Evil's Avatar
    Join Date
    Mar 2000
    Location
    Columbus, OH
    Posts
    105
    If you are selecting multiple fields you will need to remove the DISTINCT keyword and add each field that you have in the SELECT string to the GROUP BY.

    EX:
    SELECT MachineInfo.Machineno,
    MachineInfo.Division,
    MachineMaintenance.RepairTime,
    MachineMaintenance.SetupTime,
    MachineMaintenance.PMTime,
    ([MachineMaintenance]![RepairTime])+ ([MachineMaintenance]![SetupTime])+([MachineMaintenance]![PMTime]) AS Total

    FROM MachineInfo
    INNER JOIN MachineMaintenance
    ON MachineInfo.Machineno = MachineMaintenance.Machineno;

    GROUP BY MachineInfo.Machineno,
    MachineInfo.Division,
    MachineMaintenance.RepairTime,
    MachineMaintenance.SetupTime,
    MachineMaintenance.PMTime,
    Total

    I can't test this code but it should at least give you an idea of how to use the GROUP BY clause. Good Luck!
    Dr_Evil
    Senior Programmer
    VS6 EE
    VS.NET EA

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Aug 2000
    Location
    Fremont Ohio 43420
    Posts
    253
    Thanks for the help Dr_Evil. But the query still is not showing only one Machineno and one total for all machineno's. I read that DistinctRow eliminates duplicate rows based on all columns of all tables included in the query. So I tried putting the distinctrow back into the sql statement, but it also did not have any effect.

    Do you have any other ideas?

    thanks again

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Aug 2000
    Location
    Fremont Ohio 43420
    Posts
    253

    Talking

    Thanks for all the help Dr_Evil. I finally figured out how to get the group I want and all the totals.

    jeffro!

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