Excel formulas to combine columns and convert time, More SQL functions

Today I learned:

Excel Formula to Combine Columns

=[@Column1] & [@Column2]

For example: here is how I’d combine columns of hours and minutes and put a colon in-between: =[@Hour] & ":" & [@Minute]

Convert hours.minutes to hh:mm:ss in Excel

Take 275.75 and convert it to 275:45 =CELL/24 converts the hours.minutes to days Then you format the column by Custom > Time > 37:30:55 (hh:mm:ss)

SQL Dates, Concatenation, and Grouping

I learned some useful things today in SQL: SUM(), CONCAT_WS() to get CSV output, DATE() to get the date part of a datetime stamp, and GROUP BY to get the sums grouped by another column

SELECT SUM(calls), username  FROM scoreboard_calls GROUP BY username;  SELECT DATE(time), SUM(calls) FROM scoreboard_calls GROUP BY DATE(time);  SELECT CONCAT_WS(',',SUM(calls),username) "calls,user" FROM scoreboard_calls GROUP BY username ORDER BY username, SUM(calls);

Leave a Reply