Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
20181117
CREATE TABLE Offers ( [OfferID] int, [description] nvarchar(255), PRIMARY KEY( [OfferID] ) ); CREATE TABLE OrderItems ( [ordernum] int, [categoryid] int, [sku] nvarchar(255), [qty] int, [extprice] numeric(10,2), PRIMARY KEY( [OrderNum],[SKU] ) ); INSERT INTO Offers (OfferID, description) VALUES (1,'Offer1'); INSERT INTO Offers (OfferID, description) VALUES (2,'Offer2'); INSERT INTO Offers (OfferID, description) VALUES (3,'Offer3'); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 1,'SKU1', 2, 10.00); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 2,'SKU6', 2, 17.00); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 3,'SKU2', 2, 12.00); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 4,'SKU5', 2, 13.00); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 5,'SKU3', 2, 14.00); insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 6,'SKU7', 2, 18.00); select * from offers; select * from orderitems order by categoryid; select i.orderNum , sum( case when o.offerID is not null then i.extprice else 0 end ) promoSpend , sum ( case when o.offerID is null then i.extprice else 0 end ) nonPromoSpend from orderItems i left join Offers o on o.offerid = i.categoryID group by i.ordernum
run
|
edit
|
history
|
help
0
deepu
Sql Server sys.syslanguages
Combined Where and having
Sql Server's curse
class
Group by
2021.02.25 Lab 4
dbray
Basic Except
SQL injection