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!

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

Thank you both! This works without any problem.