I am trying to return the item num, item size, item colour and a count of how many items of a given colour and size there are

Question

Here is the table -

create table inventoryItem (
       itemNum    varchar(10) not null
              references itemType(itemNum) on delete cascade,
       code   varchar(20) not null,
       qtyInstock float       not null,
       itemColor  varchar(15),
       itemSize   float,
       primary key (itemNum, code)

This is the values -

insert into inventoryItem values ('A0', 'pbk', 30, 'black', 3.0);
insert into inventoryItem values ('B1', 'hbk', 50, 'white', 4.0);
insert into inventoryItem values ('A0', 'hbk', 25, 'green', 4.0);
insert into inventoryItem values ('C2', 'hbk', 20, 'black', 3.0);
insert into inventoryItem values ('C1', 'ebk', 70, 'black', 4.0);
insert into inventoryItem values ('A3', 'pbk', 30, 'black', 2.0);
insert into inventoryItem values ('A2', 'pbk', 50, 'green', 3.0);
insert into inventoryItem values ('A0', 'ebk', 15, 'green', 4.0);
insert into inventoryItem values ('C2', 'pbk', 30, 'black', 3.0);

Further explanation - I want to create a view showing the itemNum itemSize itemColor and a count of how much qtyInstock there is for each instance of a color and size. For example - how much stock is there for a black shirt in size 3 or a white shirt in size 2. I've been lost for hours and have no idea how to go about this. Please help! Thank you to anyone who comments :)

Answer 1

I believe this is your current query:

SQL> select itemnum, itemsize, itemcolor, sum(qtyinstock) qty
  2  from inventoryitem
  3  group by itemnum, itemsize, itemcolor;

ITEMNUM      ITEMSIZE ITEMCOLOR              QTY
---------- ---------- --------------- ----------
A0                  4 green                   40
A0                  3 black                   30  --> 3 black
C1                  4 black                   70
C2                  3 black                   50  --> 3 black
A2                  3 green                   50
B1                  4 white                   50
A3                  2 black                   30

7 rows selected.

SQL>

As you said, there are just too many rows for size: 3 / color: black. That's because of itemnum, so - one option is to remove it from query:

SQL> select itemsize, itemcolor, sum(qtyinstock) qty
  2  from inventoryitem
  3  group by itemsize, itemcolor;

  ITEMSIZE ITEMCOLOR              QTY
---------- --------------- ----------
         4 white                   50
         4 green                   40
         3 green                   50
         4 black                   70
         2 black                   30
         3 black                   80  --> 3 black

6 rows selected.

SQL>

Now there's only one row, but - obviously, you don't have itemnums any more. If you do need that information, one option is to aggregate them:

SQL> select listagg(itemnum, ', ') within group (order by itemnum) items,
  2         itemsize,
  3         itemcolor,
  4         sum(qtyinstock) qty
  5  from inventoryitem
  6  group by itemsize, itemcolor;

ITEMS                  ITEMSIZE ITEMCOLOR              QTY
-------------------- ---------- --------------- ----------
A3                            2 black                   30
A0, C2, C2                    3 black                   80  --> 3 black
A2                            3 green                   50
C1                            4 black                   70
A0, A0                        4 green                   40
B1                            4 white                   50

6 rows selected.

SQL>

I guess that this might be what you're looking for.