MY SQL

[AntonyRayan]

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

[Dani]

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

[AntonyRayan]

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'

[Dani]

You have a typo. It's billing.price. Not bbilling.

[AntonyRayan]

k

[AntonyRayan]

Thank Queen Dani its really working

[AntonyRayan]

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

[Dani]

Sorry I don't understand your question?

[AntonyRayan]

how you joined that two rows of pen

[Dani]

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.

[AntonyRayan]

ok good thank you

[AntonyRayan]

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).

[Dani]

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

[AntonyRayan]

ok its working but i want to know for a particular customer?

[Dani]

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.

[AntonyRayan]

    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,....

[AntonyRayan]

I did not get answer for this