Selecting Grouped Row Count When Using MySQL GROUP BY Column

You may have been using subqueries to get how many rows were grouped when using MySQL’s GROUP BY function. There’s a little trick that helps to get this number without using subqueries, more clean and faster (when the amount of rows get higher).

Let’s assume we have a table contain folks from different cities. Like this:

folk_name   |   city_name
---------------------------
evren       |   ohio
steve       |   ohio
cem         |   weston
don         |   sunderland
ritchie     |   weston
ian         |   hounslow
alican      |   ohio

When we want to get only city names (distinct) from this table, we may group the data by city_name column like this:

SELECT f.* FROM `folks` f GROUP BY f.`city_name`

And the result would be:

folk_name   |   city_name
---------------------------
ian         |   hounslow
evren       |   ohio
don         |   sunderland
cem         |   weston

The expected behaviour of GROUP BY is to select first rows so other folks from same cities do not appear. It also orders by the GROUP BY column (ascending).

Let’s say we want to get how many folks exist in each city by grouping by. First thing comes to mind could be using a subquery like this:

SELECT f1.*, (SELECT COUNT(f2.`folk_name`) FROM `folks` f2 WHERE f2.`city_name` = f1.`city_name`) AS `folk_count` FROM `folks` f1 GROUP BY `city_name`

And the result would be:

folk_name   |   city_name   |   folk_count
-------------------------------------------
ian         |   hounslow    |   1
evren       |   ohio        |   3
don         |   sunderland  |   1
cem         |   weston      |   2

Let’s admit, subqueries are messy. They make the query more complicated to read and also are question marks on performance.

How about selecting number 1 (one) for each row and sum it up on grouping by?

Doing something like this:

SELECT f.*,SUM(1) AS `folk_count` FROM `folks` f GROUP BY f.`city_name`

Would return:

folk_name   |   city_name   |   folk_count
-------------------------------------------
ian         |   hounslow    |   1
evren       |   ohio        |   3
don         |   sunderland  |   1
cem         |   weston      |   2

Nicely done.

Post a comment

Javascript: Export HTML Table to Excel With Custom File Name

Web browser wars hurt developers’ hearts. It is mostly about CSS, sometimes about JavaScript, HTML etc…

Thus I know how it feels when you expect something to get done easily appears a lot more harder than you expected.

Code below is tested only with Chrome (24+). It is making these processes:

  • Gets the HTML code of your table inside your div element.
  • Replaces the spaces in the code with correct syntax for Excel (otherwise spaces will be removed in your Excel sheet).
  • Generates a specific file name (for minutes) in order to avoid overriding old files and to supply archiving by date values.
  • And lastly and most importantly, saving the file with a custom file name.

Here’s my combination of codes (from different SO questions or tutorials) if you want to save HTML table to client computer using JavaScript code.

$(document).ready(function() {
	$("#btnExport").click(function(e) {
		//getting values of current time for generating the file name
		var dt = new Date();
		var day = dt.getDate();
		var month = dt.getMonth() + 1;
		var year = dt.getFullYear();
		var hour = dt.getHours();
		var mins = dt.getMinutes();
		var postfix = day + "." + month + "." + year + "_" + hour + "." + mins;
		//creating a temporary HTML link element (they support setting file names)
		var a = document.createElement('a');
		//getting data from our div that contains the HTML table
		var data_type = 'data:application/vnd.ms-excel';
		var table_div = document.getElementById('dvData');
		var table_html = table_div.outerHTML.replace(/ /g, '%20');
		a.href = data_type + ', ' + table_html;
		//setting the file name
		a.download = 'exported_table_' + postfix + '.xls';
		//triggering the function
		a.click();
		//just in case, prevent default behaviour
		e.preventDefault();
	});
});
74 Comments

Generate DateTime Value in PHP for Using in MySQL Command

When we work with date and/or time values in MySQL, it’s always easy to use built-in MySQL functions such as NOW(), DATE_ADD(), TIMESTAMP() etc… Now imagine you have to pass date/time values to MySQL from PHP variables. First time I tried that, it didn’t work out so easy for me.

Yes, if you simply take one column value and use it again, you have no problem. But here’s an example that you take date and time from MySQL column, add 1 seconds to that value and use it in query again:

//Here's just stuff in order to get a datetime value from MySQL DB.
$query = "SELECT TOP 1 `date` FROM `table`";
$result = mysql_query($query, $db_link);
$date_time_val = "";
if ($result) {
    while($row = mysql_fetch_assoc($result)) {
        //Here we get the value.
        $date_time_val = $row["date"];
    }
}

//Now we add one second to this value and use it again.
//First, use it for generating a DateTime variable from PHP library.
$time = new DateTime($date_time_val);
//If you need current time in PHP, you can use this:
//$time = new DateTime(date("Y-m-d H:i:s"));

//Add one second,
$time->modify("+1 seconds");

//You can not use it as it is
//You need to convert to a string in MySQL datetime format.
$time_string = $time->format("Y-m-d H:i:s");

//Now you're free to use in your query
$query_new = "SELECT * FROM `table` WHERE `date` = '".$time_string."' ";
Post a comment

T-SQL: Concatenate Multiple Rows of a Column into One Cell

Simple: You have a column and you want to take all rows as one cell. So that you can do searching, ordering, whatever you like.

Solution: Declare a variable, in this case VARCHAR or NVARCHAR, then SELECT the variable by equaling [itself] plus [column name] plus [your seperation char].

Here’s the code snippet:

DECLARE @concatenated NVARCHAR(1000)
SET @concatenated = ''
SELECT @concatenated = @concatenated + [NAME_COLUMN] + ',' FROM [USERS_TABLE] WHERE [ID_COLUMN] < 100
SELECT @concatenated
Post a comment

T-SQL: How to Order by Surname in Full Name Column?

Sometimes you need to do ordering by in a full name column. If you want to order by first name, that’s cool, but when you need to order by last name, you need to find the surname for each field.

Let’s take a look at this data:

[Full Name]
—————————-
Albert Einstein
Leonardo da Vinci
Nicolas Tesla

 
These values are ordered by first name so there’s no problem with that. But if we wanted to order by last name(which is not a particular column in this table), we had to find a way like splitting these fields or something else.

Anyway, there was a good line of code in StackOverflow (see question&answer here) written by user Recep.

SELECT [Full Name], REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name])))) AS SurnameAfterReversing
FROM GENIUS_TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE([Full Name]), 0, CHARINDEX(' ', REVERSE([Full Name]))))
--We need to select our ORDER BY expression as a new column otherwise it will warn that it can't order by a column which is not exist in query result.

It reverses the whole name here, then gets text until the first space (‘ ‘) character, then reverses the new text back. That way, we actually get the last name so you can do anything you want with this column.

And here is the data as a result of this query:

[Full Name] SurnameAfterReversing
—————————- —————————-
Albert Einstein Einstein
Nicolas Tesla Tesla
Leonardo da Vinci Vinci
1 Comment