mySQL: Combine strings into a single list

In mySQL, how do I combine strings into a single list? That is, how do I pivot them from rows into a list of values?

First, let's take a look at the data we're dealing with. We have two tables, message and recipientinfo, and we want to show all messages as rows, and instead of having one row per TO recipient, we want to have one row per message, that holds all the people listed on the TO line for each message. To do this, in mySQL, we have the GROUP_CONCAT() function, and in SQL Server, in order to combine values, we have to STUFF() them.

Now, let's take a look at the statements using the Enron data set.

mySQL

SELECT 

    m.sender,

    date,

    GROUP_CONCAT(r.rvalue) as 'TO'

FROM recipientinfo as r

right JOIN message as m ON r.mid = m.mid 

where rtype = 'TO'

GROUP BY m.sender, date

LIMIT 100;

SQL Server

SELECT

m.sender as MsgSender,

m.[date] as MsgDate,

MsgToList = STUFF((

SELECT ',' + r.rvalue

FROM dbo.recipientinfo as r

WHERE r.mid = m.mid and rtype = 'TO'

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

FROM dbo.[message] as m

Happy New Year - 2021

 Welcome to 2021!

I hope you have a great year!

New Years Eve 2020

 Wherever you are, and how ever you're celebrating, I hope you have a happy and safe New Years Eve!

mySQL: Sample Databases

 How do I load customer agnostic sample databases into mySQL?

The issue with demos and/or general purpose query learning is that it requires data. Sometimes, different types of data.

For example, for general purpose query learning, the enron dataset is amazing. Especially for windowing functions, too, where having a large number of rows in a single table is what you want. I found it here:

http://www.ahschulz.de/enron-email-data/

However, for other purposes, especially those with more of a business flavor such as aggregates and groups, something like the Northwind database is ideal.

The GitHub user jpwhite has published the Northwind sample database as a set of mySQL scripts.

https://github.com/jpwhite3/northwind-MySQL

mySQL: Basic aggregates from a table

In mySQL, how do I get the basic aggregates from a table? 

mySQL	Explanation	SQL Server
use enron; select COUNT(*) as counted from recipientinfo; select MIN(rid) as rid_min from recipientinfo; select MAX(rid) as rid_max from recipientinfo; select SUM(rid/10.0) as rid_summed from recipientinfo; select AVG(rid/10.0) as rid_avgd from recipientinfo;	No real surprises here - they work the same in both places.	use Enron; go select COUNT(*) as counted from dbo.recipientinfo; select MIN(rid) as rid_min from dbo.recipientinfo; select MAX(rid) as rid_max from dbo.recipientinfo; select SUM(rid/10.0) as rid_summed from dbo.recipientinfo; select AVG(rid/10.0) as rid_avgd from dbo.recipientinfo;

mySQL: How do I find the number of rows in my table

 How do I find the number of rows in my table?

In mySQL:

select count(*) from enron.message;

or

show table status;

SQL Server:

use enron;

go

select count(*) from dbo.message;

or

use enron;

go

select * from sys.partitions where object_id = OBJECT_ID('dbo.message');

Merry Christmas!

 It's Christmas - on a Friday :)

Merry Christmas. I hope you and your family are safe and healthy at this time of year. 

Especially this year.