workorders.sql

SELECT 
	sum(Total) AS Total, State 
FROM (
		SELECT 
			State, Total 
		FROM 
			WorkOrders 
		INNER JOIN 
			Customers ON CID = ID 
		WHERE Type != "Warranty" 
	UNION 
		SELECT 
			State, 80 
		FROM 
			WorkOrders 
		INNER JOIN 
			CustomersRetail ON CID=ID 
		WHERE Type = "Warranty"
) 
GROUP BY State;

CREATE TABLE "WorkOrders" (
    "WID" integer not null primary key autoincrement,
    "CID" integer,
    "Type" varchar(255),
     "Total" integer
);

CREATE TABLE "Customers" (
    "ID" integer not null primary key autoincrement,
    "State" varchar(255)
);

CREATE TABLE "CustomersRetail" (
    "ID" integer not null primary key autoincrement, 
    "State" varchar(255)
);