Returning multiple aggregate functions as rows

Question

I need some help formulating a select statement. I need to select the total quantity shipped for each part with a distinct color. So the result should be a row with the color name and the total.

Here's my schema:

create table s
  ( sno    char(5)  not null,
    sname  char(20) not null,
    status smallint,
    city   char(15),
    primary key (sno)
  );
create table p
  ( pno    char(6)  not null,
    pname  char(20) not null,
    color  char(6),
    weight smallint,
    city   char(15),
    primary key (pno)
  );
create table sp
  ( sno    char(5)  not null,
    pno    char(6)  not null,
    qty    integer  not null,
   primary key (sno, pno)
  );

Answer 1

As your schema stands each product PNO has only one colour, so I'm not sure what your question actually requires.

Sales by product:

select p.pno
       , sum (sp.qty)
from   p join sp on (p.pno = sp.pno)
group by p.pno;

Sales by colour:

select p.color
       , sum (sp.qty)
from   p join sp on (p.pno = sp.pno)
group by p.color;

edit

To get sales for a specific colour is easy:

select sum (sp.qty)
from   p join sp on (p.pno = sp.pno)
where p.color = 'BLUE';