Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    3

    Unanswered: mysql query problem

    Hi everybody,
    I am usng MYSQL version 4.1 and PHP 5.0 on windows XP.
    I had some date related to sales, purchase and payment.
    Now I want to convert that in PHP-MYSQL based software application.
    I have four tables in a database. Tables and there structures are:

    TABLE sale: client(varchar,100),item(varchar,10),date(datetime ),rate(dcimal,14,2),quantity(decimal,14,2),deducti on1(decimal,14,2),deduction2(decimal,14,2),addtion (decimal,14,2)

    TABLE purchase: client(varchar,100),item(varchar,10),date(datetime )rate(dcimal,14,2),quantity(decimal,14,2),deductio n1(decimal,14,2),deduction2(decimal,14,2),addtion( decimal,14,2)

    TABLE payment: client(varchar,100),item(varchar,10),date(datetime ),amount(decimal,14,2),purpose(varchar,100)

    TABLE client: client(varchar,100)parimary key

    client table stores data of distinct clients.

    sale table stores data of various clients, amount, date.

    purchase table stores data of various clients, amount, date.

    daily table stores data of various clients, ampunt paid, date.

    Now I want an outstanding report for each client, for sale and purchase independantly.
    Like in these column structure:
    CLIENT TOTALSALES PAID DUE
    A 100 50 50
    B 450 100 350
    I am running this euery:
    #########################CODE##################### ##
    SELECT DISTINCT sale.client, SUM(sale.amount), SUM(daily.amount) FROM
    purchase, daily WHERE sale.client = daily.client
    ########################CODE###################### ##
    BUt ende up with wrong results. pls help with proper query

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could not possibly have run that query, because it's missing a GROUP BY clause

    what is the purpose of the "daily" table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    3
    daily table stores data of various clients, ampunt paid, date.
    I have tied the query with GROUP BY purchase.client, it is returning a result with amount of sales and amount of payment by each client multiplied by the number of times a particular client is present in daily and purchase table repectively.
    Any solution?
    thanks and regards

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ritwik
    daily table stores data of various clients, ampunt paid, date.
    isn't that what the payments table is for?

    the problems you are describing are due to trying to sum up two unrelated one-to-many relationships at the same time

    can you write separate queries for each of the tables, to get one row per customer? if you show me those, i will show you how to combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    3

    mysql problem

    This is the query to collect sum of sales amounts for each client from sales table.

    ###################################code########### ###########################################
    SELECT DISTINCT sales.client, SUM(sales.amount) FROM sales GROUP BY sales.client
    ################################################## ##############################################

    This is the query for collecting sum of paid amounts form daily(for payment)table by each client.

    #############################################code# ##########################################
    SELECT DISTINCT daily.client, SUM(daily.amount) FROM sales WHERE sales.client = S GROUP BY sales.client
    ################################################## ##########################################

    Now how can I combine them?

    thanks and regards.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first of your two queries does not require the DISTINCT --
    Code:
    SELECT client, SUM(amount) FROM sales GROUP BY client
    the second does not work by itself -- did you test it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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