Results 1 to 10 of 10

Thread: SQL Query HELP

  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: SQL Query HELP

    Hi Guys,

    Having a little bit of trouble with a SQL Query.
    [Hardware]
    -BARCODE
    -ModelID
    -BuyingPrice
    -Cost
    -AmountPaid
    -DateSold

    [Model]
    -ModelID
    -ModelName



    Show a break report of all devices sold for the current year broken down according to brand. Each table orders the hardware by model ID. The break should show the total price(SellingPrice inside HardwareTable), cost, profit, and name of that model.

    So far i have:

    SELECT Model.ModelName, avg(BuyingPrice - Hardware.Cost)
    FROM hardware, Model
    WHERE Hardware.ModelID =Model.ModelID
    AND YEAR(DateSold) = 2011
    GROUP BY Model.ModelName
    ORDER BY Model.ModelID
    And Get an Error "You tried to execute a Query that does not include the specified expression 'Model.ModelID' as part of an aggregate function.


    Also Stuck on how to go about doing:
    "Show all hardware sold in the previous year (including the current month, back to the same month last year), broken down by month."
    Copy of the database if required:
    testDatabase mdb
    Last edited by coccoster; 09-15-11 at 08:24.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove your ORDER BY clause and try again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    9
    Quote Originally Posted by r937 View Post
    remove your ORDER BY clause and try again
    I still get the same error mate.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coccoster View Post
    I still get the same error mate.
    can you show me your latest query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you use a GROUP BY clause, all columns must be part of this GROUP BY clause or must be the argument of an aggregate function (MIN(), MAX(), STD(), AVG(), ...etc.). You should try:
    Code:
    SELECT Model.ModelName, Avg([BuyingPrice]-[Hardware].[Cost]) AS Profit
    FROM Model INNER JOIN Hardware ON Model.ModelID = Hardware.ModelID
    GROUP BY Model.ModelName, Model.ModelID
    ORDER BY Model.ModelID;
    Have a nice day!

  6. #6
    Join Date
    Apr 2009
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    When you use a GROUP BY clause, all columns must be part of this GROUP BY clause or must be the argument of an aggregate function (MIN(), MAX(), STD(), AVG(), ...etc.). You should try:
    Code:
    SELECT Model.ModelName, Avg([BuyingPrice]-[Hardware].[Cost]) AS Profit
    FROM Model INNER JOIN Hardware ON Model.ModelID = Hardware.ModelID
    GROUP BY Model.ModelName, Model.ModelID
    ORDER BY Model.ModelID;
    Thank you very much for your help, worked a treat!

    How would i chose the Most Expensive item in the list?

    I Have:

    SELECT Barcode, MAX(Cost) as MostExpensive
    FROM hardware
    WHERE MONTH(DateSold)=7
    AND YEAR(DateSold)=2011
    GROUP BY Barcode
    But it returned the 3 most expensive items, i need the most expensive.

    Barcode MostExpensive
    12345678 $1,200.00
    13456788 $2,300.00
    22222221 $35.00
    Cheers.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coccoster View Post
    i need the most expensive.
    Code:
    SELECT TOP 1 Barcode, Cost as MostExpensive
      FROM hardware
     WHERE MONTH(DateSold)=7 
       AND YEAR(DateSold)=2011
    ORDER 
        BY Cost DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2009
    Posts
    9
    Quote Originally Posted by r937 View Post
    Code:
    SELECT TOP 1 Barcode, Cost as MostExpensive
      FROM hardware
     WHERE MONTH(DateSold)=7 
       AND YEAR(DateSold)=2011
    ORDER 
        BY Cost DESC
    Thanks heaps mate,

    3 More questions if you can help again.

    Show all hardware sold in the previous year (including the current month, back to the same month last year), broken down by month.
    Show a list of all staff broken down by supervisor. Show the full name and ID of the staff, and show the full name and ID of the supervisor in the break.

    Not sure how to go about those 2.

    Currently the reports are saved as a html file called report1.html, id like to make the user enter a name for the HTML file upon clicking submit.

    I currently have:

    Try
    'Open new HTML file to be written
    WriteHtml = New StreamWriter(Application.StartupPath & "\ListHardware.html")
    If Not (WriteHtml Is Nothing) Then ' Is the File Open
    'Write info stored in sPageText to the File
    WriteHtml.Write(sPageText)
    'Close the HTML file
    WriteHtml.Close()
    End If

    ' automatically load internet explorer to show the file created.
    sCommand = "IExplore.exe"
    sParam = """" & Application.StartupPath & "\ListHardware.html"""
    System.Diagnostics.Process.Start(sCommand, sParam)
    Catch ex As Exception
    MessageBox.Show(ex.Message & vbCrLf & sCommand)
    End Try
    Cheers!

  9. #9
    Join Date
    Apr 2009
    Posts
    9
    Can anybody help me with the saving the HTML file to a input entered by the user?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not used to work with HTML, sorry! I hope someone else will be able to help you.
    Have a nice day!

Posting Permissions

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