SQL help in solving queries!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: SQL help in solving queries!

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    SQL help in solving queries!

    hi am working on a project on databases, i have already created a conceptual schema design(ERD) which i translated into the logical relational schema below........

    Customer{customer_id, customer_fname, customer_lname, customer_address, customer tel no, customer_type*}

    Customer category{customer type, discount}//private =0, silver = 1.5, gold = 2.5, diamond = 4.5 //a customer has to be in one of the four categories. silver, gold and diamond customers are said to be business customers

    category{category_id, category_name}

    brand(brand id, brand name}

    supplier{supplier id, supplier name, supplier address}

    equipment{equipment id, equipment_name,category id*, brand id*, supplier id*, price, delivery time}

    Transaction{transaction_id, equipment_id*, customer_id*, return_date, cancellation_reason, service_type//refund or replacement}

    now the last part of my assignment expects me to carry out sql queries and am not very good with sql. the queries are below and some of the work i have tried on them.

    The queries

    1. For a given particular equipment, show current stock and current items on hire with expected return dates. the name of the equipment should be captured at run-time from the user.

    am still working on this one.

    2.for a particular business customer, show the current items on hire with expected return dates plus any previous complaints that were made by that customer that involved a replacement or refund.

    SELECT t.Equipment_id, t.Return_date, t.Cancellation_Reason, t.Support
    FROM [Transaction] AS t, Customer AS c, Membership AS m
    WHERE t.Customer_id=c.Customer_id And m.Discount>1;

    this solution does not give me the desired results

    3.produce, for a given category, the total number of equipmets(i.e. items) under that category available in stock and the number currently on hire to customers.

    4. A list of names and addresses of all suppliers along with the total number of equipments from all categorys that they currently supply.

    SELECT S.Supplier_name, S.Supplier_address, COUNT(E.Supplier_id) AS Total
    FROM Supplier AS S, Equipment AS E
    WHERE S.Supplier_id=E.Suppler_id;

    i thought i had actually got this right but it is giving me an error that "supplier_name" is not part of an aggregate function.

    .......................................................Any help will be appreciated.

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Welcome to DevX

    When using aggregates (such as SUM, COUNT, MIN, MAX, etc) you MUST include ALL fields in the SELECT statement NOT involved in the aggregate, in an ORDER BY clause.

    In this case, your SQL query needs to read
    Code:
    SELECT S.Supplier_name, S.Supplier_address, COUNT(E.Supplier_id) AS Total
    FROM Supplier AS S, Equipment AS E
    WHERE S.Supplier_id=E.Suppler_id
    ORDER BY S.Supplier_name, S.Supplier_address
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Mar 2009
    Posts
    3

    thanx

    Thanx for the heads up. will look it up and make adjustments.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    When you have a moment, skim through this.

    http://www.w3schools.com/sql/sql_groupby.asp
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Mar 2009
    Posts
    3

    Thanx

    Thanx for the help, i looked it up and got more than i bargained for. anyway i have rearranged my coding for the two question that am making progress on but for .

    Q 2. i have come up with this code

    Code:
    SELECT t.Equipment_id, t.Return_date, t.Cancellation_reason, t.Support
    
    FROM [Transaction] AS t, Customer AS c
    
    WHERE Customer_name=c.Customer_name And c.Customer_id=t.Customer_id And c.Discount>0
    
    ORDER BY Equipment_id, Return_date, Cancellation_reason, Support;
    the code gives me the required results but how can i get the user to input a name, then after that give the resultset for that particular user.

    Q4. i have come up with this code

    SELECT s.Supplier_name, s.Supplier_address, COUNT(e.Supplier_id) AS Total
    FROM Supplier AS s, Equipment AS e
    WHERE s.Supplier_id=t.Supplier_id
    GROUP BY e.Supplier_id
    ORDER BY s.Supplier_name, s.Supplier_address;

    but it is still giving me this error "you tried to execute a query that does not include the specified expression 'Supplier_name' as part of an agregate function"
    where have i gone wrong? i have attached my database in case you want to see the queries as i have wrote them.
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by mo_green View Post
    but it is still giving me this error "you tried to execute a query that does not include the specified expression 'Supplier_name' as part of an agregate function"
    where have i gone wrong?
    Look at your GROUP BY clause.

    Now, consider this
    Quote Originally Posted by Hack View Post
    When using aggregates (such as SUM, COUNT, MIN, MAX, etc) you MUST include ALL fields in the SELECT statement NOT involved in the aggregate, in an ORDER BY clause.
    You do not have all the fields in your select statement NOT involved in the aggregate. You have the ONE field in your select that is involved. That is the problem.

    Go back to Post #2 and look at the GROUP BY that I used.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by mo_green View Post
    the code gives me the required results but how can i get the user to input a name, then after that give the resultset for that particular user.
    Are you doing this in Visual Basic or Access VBA? If so, what version?

    Add a textbox to the screen and use the contents to return results for a particular user.
    Code:
    SELECT s.Supplier_name, s.Supplier_address, COUNT(e.Supplier_id) AS Total
    FROM Supplier AS s, Equipment AS e
    WHERE s.Supplier_id=t.Supplier_id
    AND fieldintableforcustomername = '" & Text1.Text & "' " '<====== add this to your form
    GROUP BY e.Supplier_id  '<====== fix your group by of course
    ORDER BY s.Supplier_name, s.Supplier_address;
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. find version & service packs
    By rperez in forum Database
    Replies: 5
    Last Post: 01-02-2009, 04:14 PM
  2. Help me to convert SQL queries to Oracle!!!
    By dehghan in forum Database
    Replies: 1
    Last Post: 11-16-2006, 06:15 PM
  3. Javabean to incorporate sql queries
    By davebent32 in forum Java
    Replies: 0
    Last Post: 03-14-2005, 07:03 PM
  4. SQL joins and/or sub queries
    By Roxanne in forum Database
    Replies: 2
    Last Post: 08-23-2002, 07:49 AM
  5. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center