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);
This is what I have tried -
select itemSize, itemColor, SUM(qtyInstock ) as stock_combination
from inventoryItem
group by itemSize, itemColor;
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 :)
CodePudding user response：
Your query gives what you indicate you are looking for, except you cannot add itemNum and get the correct total as you would need to add it to the group by. (I guess that is what you are discovering.) The solution is the Analytic function version of sum. (See demo)
select itemNum
, itemSize
, itemColor
, qtyInstock
, sum(qtyInstock ) over (partition by itemSize, itemColor) as stock_combination
from inventoryItem
order by itemSize, itemColor, itemNum;
I added the qtyinstock for each as without it the results seem distorted, and perhaps still as it does not include code. But that is easily removed.
CodePudding user response：
Fetch all perm/combination of color and size against itemnum. Will return 0 as stock qty if that combination does not exist in the inventory -
Actual table -
select * from inventoryitem;
ITEMNUM CODE QTYINSTOCK ITEMCOLOR ITEMSIZE
---------- ----- ---------- --------------- ----------
A0 pbk 30 black 3
B1 hbk 50 white 4
A0 hbk 25 green 4
C2 hbk 20 black 3
C1 ebk 70 black 4
A3 pbk 30 black 2
A2 pbk 50 green 3
A0 ebk 15 green 4
C2 pbk 30 black 3
9 rows selected.
Main Query
select
inum,icol,isize, sum(qnty) as total_qty from
(
with cte as
(
select a.itemnum as inum,b.itemcolor as icol,c.itemsize as isize, 0 as qnty from
(select distinct itemnum from inventoryitem) a,
(select distinct itemcolor from inventoryitem) b,
(select distinct itemsize from inventoryitem) c
)
select cte.inum,cte.icol,cte.isize,cte.qnty from cte
union
select itemnum,itemcolor, itemsize, qtyInstock from inventoryItem
)
group by inum,icol,isize
order by inum,icol;
INUM ICOL ISIZE TOTAL_QTY
---------- --------------- ---------- ----------
A0 black 2 0
A0 black 3 30
A0 black 4 0
A0 green 2 0
A0 green 3 0
A0 green 4 40
A0 white 2 0
A0 white 3 0
A0 white 4 0
A2 black 2 0
A2 black 3 0
A2 black 4 0
A2 green 2 0
A2 green 3 50
A2 green 4 0
A2 white 2 0
A2 white 3 0
A2 white 4 0
A3 black 2 30
A3 black 3 0
A3 black 4 0
A3 green 2 0
A3 green 3 0
A3 green 4 0
A3 white 2 0
A3 white 3 0
A3 white 4 0
B1 black 2 0
B1 black 3 0
B1 black 4 0
B1 green 2 0
B1 green 3 0
B1 green 4 0
B1 white 2 0
B1 white 3 0
B1 white 4 50
C1 black 2 0
C1 black 3 0
C1 black 4 70
C1 green 2 0
C1 green 3 0
C1 green 4 0
C1 white 2 0
C1 white 3 0
C1 white 4 0
C2 black 2 0
C2 black 3 50
C2 black 4 0
C2 green 2 0
C2 green 3 0
C2 green 4 0
C2 white 2 0
C2 white 3 0
C2 white 4 0
54 rows selected.