Adding a date column to a query result
I have the following table and want to add an extra column to the end.
running_data:
name miles_ran date josh 1 2020-01-01 troy 2 2020-01-01 mark 1 2020-01-01 josh 1 2020-01-01 troy 1 2020-01-02 I want to group by person and see how many miles they ran on a certain date, so I came up with the following query.
SELECT name, SUM(miles_ran) FROM running_data WHERE date = '2020-01-01' GROUP BY name; This query results in this:
name miles_ran_sum josh 2 troy 2 mark 1 However, I want to add a column at the end that says ‘2020-01-01’ for all of the rows. How would I do that like this?
name miles_ran_sum date josh 2 2020-01-01 troy 2 2020-01-01 mark 1 2020-01-01 Thanks.
Just add a column with a literal, fixed value to the select clause:
SELECT name, SUM(miles_ran) total_miles_ran, '2020-01-01' date FROM running_data WHERE date = '2020-01-01' GROUP BY name; 
You can use Analytic function to get this data –
Since you need data only for a specific date, i have added one condition in SQL. you can please remove it if you opt to pull data for all dates.
Select distinct t.name, t.miles_ran_sum, t.date from (SELECT name, SUM(miles_ran) over (partition by name, date order by name) miles_ran_sum, date FROM running_data where date = ‘2020-01-01’) t;
Alternate way –
select name, date, sum(miles_ran) as miles_ran_sum from running_data where date = '2020-01-01' group by name, date; 
