CODEWITHSUNDEEP
mariadb
Florian7843
Florian7843

Reputation: 293

MariaDB How to group by 2 or more columns combined

I want to get 1 entry per day per hour from my MariaDB database.

I have a table structured like this (with some more columns):

+------------+-----------+
| dayOfMonth | hourOfDay |
+------------+-----------+

Let's assume this table is filled like this:

+------------+-----------+
| dayOfMonth | hourOfDay |
+------------+-----------+
|         11 |         0 |
|         11 |         0 |
|         11 |         1 |
|         12 |         0 |
|         12 |         0 |
|         12 |         1 |
+------------+-----------+

What I want to get is this(in fact all columns) (Every hourOfDay for each dayOfMonth):

+------------+-----------+
| dayOfMonth | hourOfDay |
+------------+-----------+
|         11 |         0 |
|         11 |         1 |
|         12 |         0 |
|         12 |         1 |
+------------+-----------+

I was able to achieve this with this statement, but it would become way too long if I want to do this for an entire month:

(SELECT * FROM table WHERE dayOfMonth = 11 GROUP BY hourOfDay) 
UNION 
(SELECT * FROM table WHERE dayOfMonth = 12 GROUP BY hourOfDay)

Upvotes: 0

Views: 283

Answers (2)

Rick James
Rick James

Reputation: 142278

Your question is unclear. This will transform your initial data into your proposed data:

SELECT DISTINCT
        dayOfMonth, hourOfDay
    FROM tbl;

"Every hourOfDay" -- do you want all hours 24 rows per day? Of so, see the "sequence table" (eg, seq_0_to_23) feature in MariaDB.

Upvotes: 0

forpas
forpas

Reputation: 164089

You can group by dayOfMonth, hourOfDay:

SELECT dayOfMonth, hourOfDay 
FROM table 
GROUP BY dayOfMonth, hourOfDay
ORDER BY dayOfMonth, hourOfDay

This way you can't select other columns (if they exist), only aggregate on them with MIN(), MAX(), AVG() etc.
Or use DISTINCT:

SELECT DISTINCT dayOfMonth, hourOfDay 
FROM table 
ORDER BY dayOfMonth, hourOfDay

Upvotes: 1

Related Questions