Count of sales partitioned by DOW (with date and time as input) - postgresql

Question

Have scoured the internet for right response, but am not finding what I want.

I have an example dataset as follows:

Date --------------------------------- Number of Sales

Saturday 9th September 13:22:00 ------ 1
Sunday 10th September 16:44:02  ------ 3 
Monday 11th September 12:00:00  ------ 2
Tuesday 12th September 13:04:34 ------ 4
Wednesday 13th September 15:84:04 ---- 3
Thursday 14th September 16:30:00 ----- 9
Friday 15th September 17:00:00  ------ 3
Saturday 16th September 18:00:03 ----- 5
Sunday 17th September 12:00:34 ------- 2

I want the output to be a table as follows:

Day of week -------------- Count
Mon ---------------------- 2
Tues --------------------- 4
Wed ---------------------- 3
Thurs -------------------- 9
Fri ---------------------- 3
Sat ---------------------- 6
Sun ---------------------- 5

This is a small sample, the dates go much further back, but this should give you an idea.

I am using postresql, version 9.5.8

I've tried different variations such as date_trunc, and partition (but perhaps am not using it properly). I keep getting the wrong output. Essentially, I want to be able to make a bar chart of day of week and count.

Ive tried this:

SELECT count(s.created_at), date_trunc('day', s.created_at)
FROM "sales" s 
GROUP BY date_trunc('day', s.created_at)

This however gives me the count per unique day, rather than simply by dow, irrespective of date.

Thank you in advance.

Answer 1

https://www.postgresql.org/docs/current/static/functions-datetime.html

date_part or extract should do:

SELECT count(s.created_at), extract(dow from s.created_at)
FROM "sales" s 
GROUP BY extract(dow from s.created_at)