mysql query problem

[ritwik]

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),deduction1(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),deduction1(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

 

Data enetered into the sales,purchaseand payment table ae with respect to client table,i.e. name of teh client stored in this table.

 

Sale amount of an item for a client is calculated as rate*quantity+addtion-deduction1-deduction2 and so is purchse calculated.

 

payment table stores data of payments of various clients paid for the sales on a diferrent date and payments made to the cleints for purchase on a diferrent date.

 

Now I want to generate a report of due statement, in this format--

client totalamount paid balance

 

and also same for the purchase(payemnts to be made).

 

What shall be my mysql queries and how can i display data feteched and calculated to the required format(in HTML tables).

 

Please help to solve this problem . Thanks and regards.

 

 

 

 

 

 

[fenway]

What do you have so far?

[ritwik]

Thanks for your response.

 

I can INSERT, UPDATE and DELETE data from all the tables. Now I want to get a due report for Sale as well as Purchase.

 

Query I am using for that is:

 

###########CODE########################################################

SELECT DISTINCT purchase.client AS CLIENT, SUM(purchase.amount) AS TOTAL , SUM(daily.amount) FROM purchase, daily WHERE  daily.client = purchase.client GROUP BY purchase.client

#########################CODE##########################################

Output of this is executed sucessfully but the problem is that it is doing the sum for each client, several time, that is if if X is a client and he has two records in daily table, the output is showing the sum as twice of the actual.

I thing I have to use the statement a bit differently, but cant understand how.

Please help me to solve the problem.

thanks and regards

[fenway]

What is the "daily" table? Should a client have two records or not? It sounds like you'll need to get distinct daily rows first.

[ritwik]

`daily` Table stores data of payments of different clients, and `purchase` table stores values of different clients and their purchase amount.

When I run the query, I am using DISTINCT to get the distinct rows from `purchase` table.

 

[fenway]

DISTINCT happens too late.

 

I'm not sure I understand what you want to SUM -- if there are multiple payments for each client, don't you want all of them?

[ritwik]

Yes I do.

 

I want sum of total amount of sale and total amount of payment for each client in a single row as output.

 

thanks and regards

[ritwik]

Yes I do.

 

I want total amount of sale and total amount of payment for each client in a single row as output.

 

thanks and regards

[ritwik]

I mam sorry.It should be:

 

I want total amount of sale and total amount of payment for each client in a single row as output.

 

thanks and regards

[fenway]

I'm confused... show us some sample output, and what's wrong with ti.