Point System Alpha, MySQL

Point System Alpha

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table EO_Incidents25;

CREATE TABLE EO_Incidents25 (ID INTEGER PRIMARY KEY,
    owner TEXT,
    type TEXT,
    division TEXT, 
    class INTEGER,                       
    ironium_alert INTEGER);
    
INSERT INTO EO_Incidents25 VALUES(1, "Kuro", "HZ Expansion", "Operations", 3, 1);
INSERT INTO EO_Incidents25 VALUES(2, "Kuro", "HZ Expansion", "Operations", 3, 1);
INSERT INTO EO_Incidents25 VALUES(3, "Kuro", "HZ Expansion", "Operations", 3, 1);
INSERT INTO EO_Incidents25 VALUES(4, "Kuro", "HZ Expansion", "Operations", 3, 1);
INSERT INTO EO_Incidents25 VALUES(5, "Lavender", "Override Escalation", "Support", 1, 0);
INSERT INTO EO_Incidents25 VALUES(6, "Lavender", "Override Escalation", "Security", 1, 0);
INSERT INTO EO_Incidents25 VALUES(7, "Lavender", "Override Escalation", "Security", 1, 0);
INSERT INTO EO_Incidents25 VALUES(8, "Lavender", "Override Escalation", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(9, "Lavender", "Override Escalation", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(10, "Lavender", "Override Escalation", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(11, "Zinnwald", "Prank Call", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(12, "Zinnwald", "Conference", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(13, "Kuro", "Prank Call", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(14, "Kuro", "Conference", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(15, "Kuro", "Visitor", "Operations", 1, 1);
INSERT INTO EO_Incidents25 VALUES(16, "Kuro", "Explosive", "Operations", 5, 4);
INSERT INTO EO_Incidents25 VALUES(17, "Zinnwald", "Suspicious Person", "Security", 2, 1);
INSERT INTO EO_Incidents25 VALUES(18, "Zinnwald", "Conference", "Security", 1, 0);
INSERT INTO EO_Incidents25 VALUES(19, "Zinnwald", "Override Escalation", "Security", 1, 0);
INSERT INTO EO_Incidents25 VALUES(20, "Kuro", "Override Escalation", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(21, "Lavender", "Conference", "Operations", 1, 0);
INSERT INTO EO_Incidents25 VALUES(22, "Zinnwald", "Raid", "Security", 4, 0);
INSERT INTO EO_Incidents25 VALUES(23, "Kuro", "Raid", "Security", 4, 0);

#select owner, 
#(select (count(type))*1 from EO_Incidents25 where type like "%Escalation%") as ORR,
#(select (count(type))*3 from EO_Incidents25 where type like "%HZ%") as HZE,
#(select (count(type))*1 from EO_Incidents25 where type like "%Prank%") as XXX,
#(select (count(type))*2 from EO_Incidents25 where type like "%Visitor%") as VIS,
#(select (count(type))*4 from EO_Incidents25 where type like "%Explosive%") as EXP,
#(select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%") as SPP,
#(select (count(type))*2 from EO_Incidents25 where type like "%Conference%") as CON,
#(select (count(type))*3 from EO_Incidents25 where type like "%Raid%") as RAD

#from EO_Incidents25

#group by owner;

select owner,
(select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Kuro") as ORRx1,
(select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Kuro") as HZEx3,
(select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Kuro") as XXXx1,
(select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Kuro") as VISx2,
(select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Kuro") as EXPx4,
(select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Kuro") as SPPx2,
(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Kuro") as CONx2,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Kuro") as RADx3,
(select (count(type)) from EO_Incidents25 where type like "%Raid%" and owner = "Kuro")
    +(select (count(type)) from EO_Incidents25 where type like "%Conference%" and owner = "Kuro") 
        + (select (count(type)) from EO_Incidents25 where type like "%Suspicious%" and owner = "Kuro") 
        + (select (count(type)) from EO_Incidents25 where type like "%Explosive%" and owner = "Kuro")
        + (select (count(type)) from EO_Incidents25 where type like "%Visitor%" and owner = "Kuro")
        + (select (count(type)) from EO_Incidents25 where type like "%Prank%" and owner = "Kuro")
        + (select (count(type)) from EO_Incidents25 where type like "%HZ%" and owner = "Kuro")
        + (select (count(type)) from EO_Incidents25 where type like "%Escalation%" and owner = "Kuro")
        as Incidents,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Kuro")
    +(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Kuro") 
        + (select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Kuro") 
        + (select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Kuro")
        + (select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Kuro")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Kuro")
        + (select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Kuro")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Kuro")
        as Points

from EO_Incidents25
where owner = "Kuro"
group by owner;

select owner,
(select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Lavender") as ORRx1,
(select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Lavender") as HZEx3,
(select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Lavender") as XXXx1,
(select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Lavender") as VISx2,
(select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Lavender") as EXPx4,
(select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Lavender") as SPPx2,
(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Lavender") as CONx2,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Lavender") as RADx3,
(select (count(type)) from EO_Incidents25 where type like "%Raid%" and owner = "Lavender")
    +(select (count(type)) from EO_Incidents25 where type like "%Conference%" and owner = "Lavender") 
        + (select (count(type)) from EO_Incidents25 where type like "%Suspicious%" and owner = "Lavender") 
        + (select (count(type)) from EO_Incidents25 where type like "%Explosive%" and owner = "Lavender")
        + (select (count(type)) from EO_Incidents25 where type like "%Visitor%" and owner = "Lavender")
        + (select (count(type)) from EO_Incidents25 where type like "%Prank%" and owner = "Lavender")
        + (select (count(type)) from EO_Incidents25 where type like "%HZ%" and owner = "Lavender")
        + (select (count(type)) from EO_Incidents25 where type like "%Escalation%" and owner = "Lavender")
        as Incidents,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Lavender")
    +(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Lavender") 
        + (select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Lavender") 
        + (select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Lavender")
        + (select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Lavender")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Lavender")
        + (select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Lavender")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Lavender")
        as Points

from EO_Incidents25
where owner = "Lavender"
group by owner;

select owner,
(select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Zinnwald") as ORRx1,
(select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Zinnwald") as HZEx3,
(select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Zinnwald") as XXXx1,
(select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Zinnwald") as VISx2,
(select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Zinnwald") as EXPx4,
(select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Zinnwald") as SPPx2,
(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Zinnwald") as CONx2,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Zinnwald") as RADx3,
(select (count(type)) from EO_Incidents25 where type like "%Raid%" and owner = "Zinnwald")
    +(select (count(type)) from EO_Incidents25 where type like "%Conference%" and owner = "Zinnwald") 
        + (select (count(type)) from EO_Incidents25 where type like "%Suspicious%" and owner = "Zinnwald") 
        + (select (count(type)) from EO_Incidents25 where type like "%Explosive%" and owner = "Zinnwald")
        + (select (count(type)) from EO_Incidents25 where type like "%Visitor%" and owner = "Zinnwald")
        + (select (count(type)) from EO_Incidents25 where type like "%Prank%" and owner = "Zinnwald")
        + (select (count(type)) from EO_Incidents25 where type like "%HZ%" and owner = "Zinnwald")
        + (select (count(type)) from EO_Incidents25 where type like "%Escalation%" and owner = "Zinnwald")
        as Incidents,
(select (count(type))*3 from EO_Incidents25 where type like "%Raid%" and owner = "Zinnwald")
    +(select (count(type))*2 from EO_Incidents25 where type like "%Conference%" and owner = "Zinnwald") 
        + (select (count(type))*2 from EO_Incidents25 where type like "%Suspicious%" and owner = "Zinnwald") 
        + (select (count(type))*4 from EO_Incidents25 where type like "%Explosive%" and owner = "Zinnwald")
        + (select (count(type))*2 from EO_Incidents25 where type like "%Visitor%" and owner = "Zinnwald")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Prank%" and owner = "Zinnwald")
        + (select (count(type))*3 from EO_Incidents25 where type like "%HZ%" and owner = "Zinnwald")
        + (select (count(type))*1 from EO_Incidents25 where type like "%Escalation%" and owner = "Zinnwald")
        as Points

from EO_Incidents25
where owner = "Zinnwald"
group by owner;


run \| edit \| history \| help	0