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

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

ElNuni
1#
ElNuni Published in 2017-09-13 11:22:05Z
 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.
Vao Tsun
2#
Vao Tsun Reply to 2017-09-13 11:44:00Z
 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) 
 You need to login account before you can post.
Processed in 0.332054 second(s) , Gzip On .