0

Here is my table ..and i want this output: 

pid | pname | custname | quantity | total base price | sale price | profit

schema:

create table customers
(
    cid int,
    cname varchar(1000),
    cg varchar(1000)
)

create table prod
(
    pid int,
    pname varchar(1000),
    baseprice int,
    saleprice int
)

create table orders
(
    oid int,
    custid int,
    pid int,
    quantity int,
    odate date
)

How do I write a query for this?

Gilad Green
  • 36,708
  • 7
  • 61
  • 95
jhon
  • 3
  • 2

2 Answers2

0
--pid | pname | custname | quantity | total base price | sale price | profit
select o.pid, p.pname, c.cname AS custname, SUM(o.quantity) AS quantity, 
SUM(p.baseprice) AS 'total base price', SUM(p.saleprice) AS 'sale price', 
SUM(p.baseprice) - SUM(p.saleprice) AS profit -- change this math to what you need
from orders o join prod p
on o.pid = p.pid
join customers c
on o.custid = c.cid
group by o.pid, p.pname, c.cname
R. Richards
  • 24,603
  • 10
  • 64
  • 64
  • Actually ..it's working but i am not understood after from orders o join prod p on o.pid = p.pid join customers c on o.custid = c.cid group by o.pid, p.pname, c.cname can u please explain me what u did here – jhon Jul 12 '16 at 12:07
  • Any answer please. – jhon Jul 12 '16 at 12:16
  • I am not 100% sure what you are asking, but the query is using letters (o,p,c) to alias the tables so you don't have to use the full table name in the join later. And, the `group by` clause is necessary in order to `SUM` the values in the `select` clause. There, you group by the field(s) that you want to roll up values to. If this is a new concept, don't be too concerned, grouping is one of those things that can take a minute to grasp. – R. Richards Jul 12 '16 at 12:17
  • Please, do not demand a comment, or an answer. I do this for fun, not as a job. – R. Richards Jul 12 '16 at 12:18
  • thanks problem solved – jhon Jul 12 '16 at 12:33
  • Glad to help! Have a good one. – R. Richards Jul 12 '16 at 12:33
0
select 
    a.pid , 
    a.pname , 
    c.cname , 
    b.quantity  , 
    a.baseprice * b.quantity as TotalBasePrice , 
    a.saleprice*b.quantity as TotalSalePrice ,
    a.saleprice*b.quantity - a.baseprice * b.quantity as profit   
from prod as a 
    inner join orders as b on a.pid = b.pid 
    inner join customers as c on b.custid = c.cid
Behnam
  • 1,039
  • 2
  • 14
  • 39