/*
Example of: group by
*/

-- ******************************************
-- Setup the tables and data
-- ******************************************

create table mycustomer (
   custnum varchar(20) NOT NULL,
   custname varchar(50) NOT NULL
);
create table myorder (
   ordernum varchar(20) NOT NULL, 
   orderdatetime timestamp NOT NULL,
   custnum varchar(20) NOT NULL
);
create table myitem (
   itemnum varchar(20) NOT NULL,
   itemtitle varchar(50) NOT NULL
);
create table myorderitem (
   ordernum varchar(20) NOT NULL,
   itemnum varchar(20) NOT NULL
);
commit;
-- myitem
insert into myitem (itemnum, itemtitle) 
  values ('BOK001', 'SQL for Oracle Survival Guide');
insert into myitem (itemnum, itemtitle) 
  values ('BOK002', 'SQL for MSSQL Survival Guide');
insert into myitem (itemnum, itemtitle) 
  values ('BOK003', 'SQL for MySQL Survival Guide');
insert into myitem (itemnum, itemtitle) 
  values ('BOK004', 'Java Survival Guide');
insert into myitem (itemnum, itemtitle) 
  values ('BOK005', 'Spring Survival Guide');
  
commit;
-- mycustomer
insert into mycustomer (custnum, custname) values ('CMP001', 'AAA Company');
insert into mycustomer (custnum, custname) values ('CMP002', 'ABC Company');
insert into mycustomer (custnum, custname) values ('CMP003', 'XYZ Company');
commit;
-- myorder
insert into myorder  (ordernum, orderdatetime, custnum ) 
  values ('ORD101', sysdate-1,'CMP001');
insert into myorder  (ordernum, orderdatetime, custnum ) 
  values ('ORD102', sysdate,'CMP001');
insert into myorder  (ordernum, orderdatetime, custnum ) 
  values ('ORD103', sysdate,'CMP001');
insert into myorder  (ordernum, orderdatetime, custnum ) 
  values ('ORD201', sysdate,'CMP002');
insert into myorder  (ordernum, orderdatetime, custnum ) 
  values ('ORD301', sysdate,'CMP003');
commit;  
-- myorderitem
insert into myorderitem (ordernum, itemnum)
  values ('ORD101', 'BOK001');
insert into myorderitem (ordernum, itemnum)
  values ('ORD101', 'BOK004');
insert into myorderitem (ordernum, itemnum)
  values ('ORD101', 'BOK005');
insert into myorderitem (ordernum, itemnum)
  values ('ORD102', 'BOK002');
insert into myorderitem (ordernum, itemnum)
  values ('ORD103', 'BOK003');
insert into myorderitem (ordernum, itemnum)
  values ('ORD201', 'BOK004');
insert into myorderitem (ordernum, itemnum)
  values ('ORD301', 'BOK005');

-- ******************************************
-- Display all items in an order for all customers
-- ******************************************
select
  cust.custnum,
  cust.custname,
  ord.ordernum,
  to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime"  
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"orderdatetime"
"CMP001"	"AAA Company"	"ORD101"	"05/26/2014"
"CMP001"	"AAA Company"	"ORD101"	"05/26/2014"
"CMP001"	"AAA Company"	"ORD101"	"05/26/2014"
"CMP001"	"AAA Company"	"ORD102"	"05/27/2014"
"CMP001"	"AAA Company"	"ORD103"	"05/27/2014"
"CMP002"	"ABC Company"	"ORD201"	"05/27/2014"
"CMP003"	"XYZ Company"	"ORD301"	"05/27/2014"
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order.
-- ******************************************

-- group by & count added.
select
  cust.custnum,
  cust.custname,
  ord.ordernum,
  to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",  
  --ord.orderdatetime,
  count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"orderdatetime"	"itemsinorder"
"CMP001"	"AAA Company"	"ORD101"	"05/26/2014"	3
"CMP001"	"AAA Company"	"ORD102"	"05/27/2014"	1
"CMP001"	"AAA Company"	"ORD103"	"05/27/2014"	1
"CMP002"	"ABC Company"	"ORD201"	"05/27/2014"	1
"CMP003"	"XYZ Company"	"ORD301"	"05/27/2014"	1
*/
/*
Results if using ord.orderdatetime vs to_char()
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"ORDERDATETIME"	"itemsinorder"
"CMP001"	"AAA Company"	"ORD101"	26-MAY-14 11.21.03.000000000 PM	3
"CMP001"	"AAA Company"	"ORD102"	27-MAY-14 11.21.03.000000000 PM	1
"CMP001"	"AAA Company"	"ORD103"	27-MAY-14 11.21.03.000000000 PM	1
"CMP002"	"ABC Company"	"ORD201"	27-MAY-14 11.21.03.000000000 PM	1
"CMP003"	"XYZ Company"	"ORD301"	27-MAY-14 11.21.03.000000000 PM	1
*/

-- ******************************************
-- Business Spec:
-- Display the # of items in an order ONLY if the customer has more than 1 order.
-- ******************************************
-- added having clause
select
  cust.custnum,
  cust.custname,
  ord.ordernum,
  to_char(ord.orderdatetime, 'mm/dd/yyyy') as "orderdatetime",  
  --ord.orderdatetime,
  count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
having count(orditm.itemnum) > 1
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"orderdatetime"	"itemsinorder"
"CMP001"	"AAA Company"	"ORD101"	"05/26/2014"	3
*/

-- ******************************************
-- Business Spec:
-- Select only the last order placed for a company. 
-- If the last date/times are the same then select the greater order number.
-- ******************************************

-- Testing how to find the last order for a customer.
-- This SQL Needs one more max!  There is 2 records because the max dates are the same value.
select ordernum
from myorder 
where custnum = 'CMP001'
and orderdatetime = (select max(orderdatetime) from myorder where custnum = 'CMP001');

/*
"ORDERNUM"
"ORD102"
"ORD103"
*/

-- Testing how to find the last order for a customer.
-- Select the last order placed only for a company. If the same date/time then the greater order number.
select max(ordernum) 
from myorder 
where custnum = 'CMP001'
and orderdatetime = (select max(orderdatetime) from myorder where custnum = 'CMP001');

/*
"MAX(ORDERNUM)"
"ORD103"
*/

-- Display the # of items in an order however only show the last item for a company.
-- Filtering is done in the where clause.  This filters data given to the group by.
select
  cust.custnum,
  cust.custname,
  ord.ordernum,
  to_char (ord.orderdatetime, 'mm/dd/yyyy') as "orderdate",
  count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
where 
  --Show only the last order placed for a company. If placed at the same time then the greater order number.
  ord.ordernum = (select max(ordernum)
                  from myorder 
                  where custnum = cust.custnum 
                  and orderdatetime = (select max(orderdatetime) from myorder where custnum = cust.custnum)
                 )
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"orderdate"	"itemsinorder"
"CMP001"	"AAA Company"	"ORD103"	"05/27/2014"	1
"CMP002"	"ABC Company"	"ORD201"	"05/27/2014"	1
"CMP003"	"XYZ Company"	"ORD301"	"05/27/2014"	1
*/


-- Display the # of items in an order however only show the last item for a company.
-- Filtering is done in the having clause. However, I would filter in the where clause instead.
select
  cust.custnum,
  cust.custname,
  ord.ordernum,
  to_char (ord.orderdatetime, 'mm/dd/yyyy') as "orderdate",
  count(orditm.itemnum) as "itemsinorder"
-- select *
from mycustomer cust
left join myorder ord on ord.custnum = cust.custnum
left join myorderitem orditm on orditm.ordernum = ord.ordernum
group by cust.custnum, cust.custname, ord.ordernum, orderdatetime
--Show only the last order placed for a company. If placed at the same time then the greater order number.
having 
  ord.ordernum = (select max(ordernum)
                  from myorder 
                  where custnum = cust.custnum 
                  and orderdatetime = (select max(orderdatetime) from myorder where custnum = cust.custnum)
                 )
order by cust.custnum, cust.custname, ord.ordernum, orderdatetime;

/*
"CUSTNUM"	"CUSTNAME"	"ORDERNUM"	"orderdate"	"itemsinorder"
"CMP001"	"AAA Company"	"ORD103"	"05/27/2014"	1
"CMP002"	"ABC Company"	"ORD201"	"05/27/2014"	1
"CMP003"	"XYZ Company"	"ORD301"	"05/27/2014"	1

*/
-- Drop all tables
drop table MYCUSTOMER;
drop table MYORDER;
drop table MYITEM;
drop table MYORDERITEM;
commit;