Help calculating percentage in SQL
(1.1) By ThanksRyan on 2021-05-17 03:09:08 edited from 1.0 [link] [source]
Hi,
I would like to know the percentage of incidents that happen based on the day of week (DoW).
Does the query need to sum all incidents, then filter based on DoW to divide part/whole*100.0
?
If so, how would I go about doing that?
schema:
CREATE TABLE trafficdata (
EventID TEXT NOT NULL UNIQUE,
EventDate TEXT,
EventTime TEXT,
EventCode TEXT,
EventDescription TEXT,
EventAddress TEXT
);
query:
select
count(EventCode) as Incidents,
substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', EventDate), 3) as DoW
from
trafficdata2
where
EventDate between ('2021-04-10')
and ('2021-04-19')
and EventCode in ('401','401A','401B','401C')
group by
DoW
order by
Incidents
desc
results:
Incidents,DoW
2,Sun
1,Thu
1,Sat
1,Mon
1,Fri
sample data:
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('1','2021-04-10','10:28:17 PM','401B','ACCIDENT (WITH INJURY)','5075 E WASHINGTON AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('2','2021-04-11','1:29:07 AM','401','ACCIDENT (TRAFFIC)','3700 STEWART AVE');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('3','2021-04-12','12:41:19 AM','438','TRAFFIC PROBLEM','2896 S LAS VEGAS BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('4','2021-04-13','12:12:07 AM','402','FIRE','3955 UNIVERSITY CENTER DR');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('5','2021-04-14','12:08:02 AM','438','TRAFFIC PROBLEM','W OAKEY BLVD / BELCASTRO ST');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('6','2021-04-15','12:11:29 AM','401C','ACCIDENT (PRIVATE PROPERTY)','3700 E BONANZA RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('7','2021-04-16','12:11:37 AM','401','ACCIDENT (TRAFFIC)','TRAILWOOD DR / CRESTDALE LN');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('8','2021-04-17','12:13:06 AM','402','FIRE','E CRAIG RD / N LAMB BLVD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('9','2021-04-18','12:10:28 AM','401','ACCIDENT (TRAFFIC)','BOULDER HWY / E DESERT INN RD');
INSERT INTO trafficdata(EventID,EventDate,EventTime,EventCode,EventDescription,EventAddress) VALUES('10','2021-04-19','12:54:32 AM','401B','ACCIDENT (WITH INJURY)','N RANCHO DR / W ALEXANDER RD');
Any pointers would be greatly appreciated!
(2) By MBL (RobMan) on 2021-05-17 09:47:40 in reply to 1.1 [source]
Did you have a view to the WINDOW functionality? You can use it to calculate the sum for the percentage for each row while you are going from row to row.
(3) By Mark Lawrence (mark) on 2021-05-17 10:39:17 in reply to 1.1 [link] [source]
As RobMan suggested a window function can give you the sum of all the counts:
100 * cast(count(EventCode) AS REAL)
/ sum(count(EventCode)) over () as Percent
(4) By ThanksRyan on 2021-05-17 17:29:46 in reply to 3 [link] [source]
Thank you both! This works without any problem.