0

This is my billing table in my sql:

id  pid     quantity    total   discount    price   customer
1   1              2    20.00   5.00          15       a
2   1              2    20.00   3.00          17.00     a
3   2              3    15.00   2.00          13.00     b
4   3             1     2.00    1.00          1.00      a

This is my product master table

id  name    mrp
1   pen     10.00
2   pencil  5.00
 3  rubber  2.00

From these two tables I got output like this. Using this query $res=mysql_query("select productmaster.name, billing.quantity,billing.total,billing.discount,billing.price from productmaster INNER JOIN billing on productmaster.id= billing.pid");

name    quantity    total   discount    price
pen     2            20.00      5.00    15
pen     2            20.00      3.00    17.00
pencil  3            15.00      2.00    13.00
rubber  1            2.00       1.00    1.00

and I want to get outputlike this .How ? Can any one do help? its urgent,....

name    quantity    total   discount    price
pen     4              40.00    8.00    32.00
pencil  3              15.00    2.00    13.00
rubber  1              2.00      1.00   1.00

Edited by Dani: Fixed formatting so only code is within code tags.

2
Contributors
16
Replies
22
Views
2 Years
Discussion Span
Last Post by AntonyRayan
Featured Replies
0

I'm just coming up with this off the top of my head (totally untested), but what about something along these lines:

SELECT p.name, SUM(b.quantity), SUM(b.total), SUM(b.discount), SUM(b.price)
FROM billing AS b
LEFT JOIN productmaster AS p ON (p.id = b.pid)
GROUP BY b.pid
ORDER BY p.name ASC
0

this is the query I executed SELECT productmaster.name, SUM(billing.quantity), SUM(billing.total), SUM(billing.discount), SUM(bbilling.price) FROM billing AS billing LEFT JOIN productmaster AS productmaster ON (productmaster.id = billing.pid) GROUP BY billing.pid ORDER BY productmaster.name ASC,,,,, and the error is 1054 - Unknown column 'bbilling.price' in 'field list'

0

i want to know from that what are the product , quantity ,price(mrp) borrowed by a particular customer for example a;

0

I pulled a report of billing, grouping the results by matching pid, and summing up the other columns. Then, I simply did a left join to fetch the friendly name for the pid from the products table.

0

Another question from that two tables is , I want to know that what are the products borrowed by a customer including quantity and price(mrp).

0

Sure, just include those columns.

SELECT p.name, p.mrp, SUM(b.quantity), SUM(b.total), SUM(b.discount), SUM(b.price)
FROM billing AS b
LEFT JOIN productmaster AS p ON (p.id = b.pid)
GROUP BY b.pid
ORDER BY p.name ASC
0

In your original post, you did not provide me with the schema for the customers table. I'm also still confused what format you want. Please give details of the customers table and explain what you want the output to look like.

0
    This is my billing table in my sql:

        id pid quantity total discount price customer
        1     1       2   20.00   5.00  15    a
        2      1      2   20.00    3.00 17.00 a
        3      2      3   15.00    2.00 13.00 b
        4      3      1   2.00     1.00 1.00  a

    This is my product master table

        id name mrp
        1 pen 10.00
        2 pencil 5.00
        3 rubber 2.00

    From these two tables I want answer like this


    customer  product quantity  price 
    a         pen     4         40     
    a         Rubber  1         2      
    b         pencil  3         15      
    and I want to get for each cutomer .How ? Can any one do help? its urgent,....

Edited by AntonyRayan: urgent

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.