Jester
Reputation: 1303
Grouped results according to one field
I have a table with 2 significant columns, one with a date and time, and another one with an amount of seconds (so, an integer).
I wish to get how many seconds I have for each day of the week, and for each hour of the day.
I managed to have this information with 24 rows for each day of the week, with the dow in one column, the hour in another one, and the amount of seconds in the last one. What I would love to have is it in just 7 rows, with one column being the dow and the other one an array or something with the 24 amounts of seconds.
Any idea on how to get rid of it?
The query I ended with is:
SELECT dow, hour, sum(duration/60) as minutes
FROM( SELECT duration,
extract( 'dow' from datetime ) as dow,
extract( 'hour' from datetime ) as hour
FROM "statistics"
WHERE datetime between '2013-10-01' and '2013-11-01'
and duration >= 300
) as foo
GROUP BY dow, hour
ORDER BY dow, hour
And this gives me a result like:
dow | hour | minuts
-----+------+--------
0 | 0 | 742
0 | 1 | 572
0 | 2 | 634
0 | 3 | 208
0 | 4 | 333
0 | 5 | 302
0 | 6 | 183
0 | 7 | 108
0 | 8 | 135
0 | 9 | 201
0 | 10 | 369
0 | 11 | 429
0 | 12 | 340
0 | 13 | 439
0 | 14 | 572
0 | 15 | 420
0 | 16 | 636
0 | 17 | 958
0 | 18 | 878
0 | 19 | 1176
0 | 20 | 995
0 | 21 | 740
0 | 22 | 783
0 | 23 | 857
1 | 0 | 474
1 | 1 | 576
1 | 2 | 431
1 | 3 | 157
1 | 4 | 140
1 | 5 | 55
1 | 6 | 17
1 | 8 | 118
1 | 9 | 356
1 | 10 | 994
1 | 11 | 930
1 | 12 | 1177
1 | 13 | 1228
1 | 14 | 896
1 | 15 | 1072
1 | 16 | 1254
1 | 17 | 1764
1 | 18 | 1303
1 | 19 | 1176
1 | 20 | 1314
1 | 21 | 1003
1 | 22 | 1161
1 | 23 | 811
...
Upvotes: 0
Views: 57
Answers (2)
Adrian Cornwell
Reputation: 196
for those not using PostgreSQL, I can think of 2 solutions.
Assuming (for this example) a results table containing the columns dow,hour and mins then you can..
1) Do a rather messy multi-join (Oracle and SQLServer versions shown in a moment) 2) or write a CONCAT_MYSTATS Function that can return the results. For SQLITE you can use the existing GROUP_CONCAT aggregate Function.
Lazy Version Code ORACLE:
SELECT d.dow,
MAX(NVL(d0.hour,0)) || '=' || SUM(NVL(d0.mins,0))
|| ';' || MAX(NVL(d1.hour,1)) || '=' || SUM(NVL(d1.mins,0))
|| ';' || MAX(NVL(d2.hour,2)) || '=' || SUM(NVL(d2.mins,0))
|| ';' || MAX(NVL(d3.hour,3)) || '=' || SUM(NVL(d3.mins,0))
|| ';' || MAX(NVL(d4.hour,4)) || '=' || SUM(NVL(d4.mins,0))
|| ';' || MAX(NVL(d5.hour,5)) || '=' || SUM(NVL(d5.mins,0))
|| ';' || MAX(NVL(d6.hour,6)) || '=' || SUM(NVL(d6.mins,0))
|| ';' || MAX(NVL(d7.hour,7)) || '=' || SUM(NVL(d7.mins,0))
|| ';' || MAX(NVL(d8.hour,8)) || '=' || SUM(NVL(d8.mins,0))
|| ';' || MAX(NVL(d9.hour,9)) || '=' || SUM(NVL(d9.mins,0))
|| ';' || MAX(NVL(d10.hour,10)) || '=' || SUM(NVL(d10.mins,0))
|| ';' || MAX(NVL(d11.hour,11)) || '=' || SUM(NVL(d11.mins,0))
|| ';' || MAX(NVL(d12.hour,12)) || '=' || SUM(NVL(d12.mins,0))
|| ';' || MAX(NVL(d13.hour,13)) || '=' || SUM(NVL(d13.mins,0))
|| ';' || MAX(NVL(d14.hour,14)) || '=' || SUM(NVL(d14.mins,0))
|| ';' || MAX(NVL(d15.hour,15)) || '=' || SUM(NVL(d15.mins,0))
|| ';' || MAX(NVL(d16.hour,16)) || '=' || SUM(NVL(d16.mins,0))
|| ';' || MAX(NVL(d17.hour,17)) || '=' || SUM(NVL(d17.mins,0))
|| ';' || MAX(NVL(d18.hour,18)) || '=' || SUM(NVL(d18.mins,0))
|| ';' || MAX(NVL(d19.hour,19)) || '=' || SUM(NVL(d19.mins,0))
|| ';' || MAX(NVL(d20.hour,20)) || '=' || SUM(NVL(d20.mins,0))
|| ';' || MAX(NVL(d21.hour,21)) || '=' || SUM(NVL(d21.mins,0))
|| ';' || MAX(NVL(d22.hour,22)) || '=' || SUM(NVL(d22.mins,0))
|| ';' || MAX(NVL(d23.hour,23)) || '=' || SUM(NVL(d23.mins,0))
FROM (SELECT distinct dow FROM mystats) d
LEFT OUTER JOIN mystats d0 ON d0.dow = d.dow AND d0.hour=0
LEFT OUTER JOIN mystats d1 ON d1.dow = d.dow AND d1.hour=1
LEFT OUTER JOIN mystats d2 ON d2.dow = d.dow AND d2.hour=2
LEFT OUTER JOIN mystats d3 ON d3.dow = d.dow AND d3.hour=3
LEFT OUTER JOIN mystats d4 ON d4.dow = d.dow AND d4.hour=4
LEFT OUTER JOIN mystats d5 ON d5.dow = d.dow AND d5.hour=5
LEFT OUTER JOIN mystats d6 ON d6.dow = d.dow AND d6.hour=6
LEFT OUTER JOIN mystats d7 ON d7.dow = d.dow AND d7.hour=7
LEFT OUTER JOIN mystats d8 ON d8.dow = d.dow AND d8.hour=8
LEFT OUTER JOIN mystats d9 ON d9.dow = d.dow AND d9.hour=9
LEFT OUTER JOIN mystats d10 ON d10.dow = d.dow AND d10.hour=10
LEFT OUTER JOIN mystats d11 ON d11.dow = d.dow AND d11.hour=11
LEFT OUTER JOIN mystats d12 ON d12.dow = d.dow AND d12.hour=12
LEFT OUTER JOIN mystats d13 ON d13.dow = d.dow AND d13.hour=13
LEFT OUTER JOIN mystats d14 ON d14.dow = d.dow AND d14.hour=14
LEFT OUTER JOIN mystats d15 ON d15.dow = d.dow AND d15.hour=15
LEFT OUTER JOIN mystats d16 ON d16.dow = d.dow AND d16.hour=16
LEFT OUTER JOIN mystats d17 ON d17.dow = d.dow AND d17.hour=17
LEFT OUTER JOIN mystats d18 ON d18.dow = d.dow AND d18.hour=18
LEFT OUTER JOIN mystats d19 ON d19.dow = d.dow AND d19.hour=19
LEFT OUTER JOIN mystats d20 ON d20.dow = d.dow AND d20.hour=20
LEFT OUTER JOIN mystats d21 ON d21.dow = d.dow AND d21.hour=21
LEFT OUTER JOIN mystats d22 ON d22.dow = d.dow AND d22.hour=22
LEFT OUTER JOIN mystats d23 ON d23.dow = d.dow AND d23.hour=23
GROUP BY d.dow
SQLSERVER:
SELECT d.dow,
CAST(MAX(ISNULL(d0.hour,0)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d0.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d1.hour,1)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d1.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d2.hour,2)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d2.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d3.hour,3)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d3.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d4.hour,4)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d4.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d5.hour,5)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d5.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d6.hour,6)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d6.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d7.hour,7)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d7.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d8.hour,8)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d8.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d9.hour,9)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d9.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d10.hour,10)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d10.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d11.hour,11)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d11.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d12.hour,12)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d12.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d13.hour,13)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d13.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d14.hour,14)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d14.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d15.hour,15)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d15.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d16.hour,16)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d16.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d17.hour,17)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d17.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d18.hour,18)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d18.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d19.hour,19)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d19.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d20.hour,20)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d20.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d21.hour,21)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d21.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d22.hour,22)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d22.mins,0)) AS VARCHAR)
+ ';' + CAST(MAX(ISNULL(d23.hour,23)) AS VARCHAR) + '=' + CAST(SUM(ISNULL(d23.mins,0)) AS VARCHAR)
FROM (SELECT distinct dow FROM mystats) d
LEFT OUTER JOIN mystats d0 ON d0.dow = d.dow AND d0.hour=0
LEFT OUTER JOIN mystats d1 ON d1.dow = d.dow AND d1.hour=1
LEFT OUTER JOIN mystats d2 ON d2.dow = d.dow AND d2.hour=2
LEFT OUTER JOIN mystats d3 ON d3.dow = d.dow AND d3.hour=3
LEFT OUTER JOIN mystats d4 ON d4.dow = d.dow AND d4.hour=4
LEFT OUTER JOIN mystats d5 ON d5.dow = d.dow AND d5.hour=5
LEFT OUTER JOIN mystats d6 ON d6.dow = d.dow AND d6.hour=6
LEFT OUTER JOIN mystats d7 ON d7.dow = d.dow AND d7.hour=7
LEFT OUTER JOIN mystats d8 ON d8.dow = d.dow AND d8.hour=8
LEFT OUTER JOIN mystats d9 ON d9.dow = d.dow AND d9.hour=9
LEFT OUTER JOIN mystats d10 ON d10.dow = d.dow AND d10.hour=10
LEFT OUTER JOIN mystats d11 ON d11.dow = d.dow AND d11.hour=11
LEFT OUTER JOIN mystats d12 ON d12.dow = d.dow AND d12.hour=12
LEFT OUTER JOIN mystats d13 ON d13.dow = d.dow AND d13.hour=13
LEFT OUTER JOIN mystats d14 ON d14.dow = d.dow AND d14.hour=14
LEFT OUTER JOIN mystats d15 ON d15.dow = d.dow AND d15.hour=15
LEFT OUTER JOIN mystats d16 ON d16.dow = d.dow AND d16.hour=16
LEFT OUTER JOIN mystats d17 ON d17.dow = d.dow AND d17.hour=17
LEFT OUTER JOIN mystats d18 ON d18.dow = d.dow AND d18.hour=18
LEFT OUTER JOIN mystats d19 ON d19.dow = d.dow AND d19.hour=19
LEFT OUTER JOIN mystats d20 ON d20.dow = d.dow AND d20.hour=20
LEFT OUTER JOIN mystats d21 ON d21.dow = d.dow AND d21.hour=21
LEFT OUTER JOIN mystats d22 ON d22.dow = d.dow AND d22.hour=22
LEFT OUTER JOIN mystats d23 ON d23.dow = d.dow AND d23.hour=23
GROUP BY d.dow
CONCAT Function Version: ORACLE:
CREATE FUNCTION CONCAT_MYSTATS (pDOW IN INTEGER)
RETURN VARCHAR2
AS
ALIST VARCHAR2(200) DEFAULT '';
BEGIN
FOR REC IN ( SELECT h.HR AS HOUR,
SUM(NVL(d.mins,0)) AS MINS
FROM (SELECT 0 AS Hr FROM dual
UNION ALL SELECT 1 AS HR FROM dual
UNION ALL SELECT 2 AS HR FROM dual
UNION ALL SELECT 3 AS HR FROM dual
UNION ALL SELECT 4 AS HR FROM dual
UNION ALL SELECT 5 AS HR FROM dual
UNION ALL SELECT 6 AS HR FROM dual
UNION ALL SELECT 7 AS HR FROM dual
UNION ALL SELECT 8 AS HR FROM dual
UNION ALL SELECT 9 AS HR FROM dual
UNION ALL SELECT 10 AS HR FROM dual
UNION ALL SELECT 11 AS HR FROM dual
UNION ALL SELECT 12 AS HR FROM dual
UNION ALL SELECT 13 AS HR FROM dual
UNION ALL SELECT 14 AS HR FROM dual
UNION ALL SELECT 15 AS HR FROM dual
UNION ALL SELECT 16 AS HR FROM dual
UNION ALL SELECT 17 AS HR FROM dual
UNION ALL SELECT 18 AS HR FROM dual
UNION ALL SELECT 19 AS HR FROM dual
UNION ALL SELECT 20 AS HR FROM dual
UNION ALL SELECT 21 AS HR FROM dual
UNION ALL SELECT 22 AS HR FROM dual
UNION ALL SELECT 23 AS HR FROM dual) h
LEFT OUTER JOIN mystats d
ON d.hour = h.HR
WHERE d.dow = pDOW
GROUP BY h.HR
ORDER BY 1
)
LOOP
--
IF NVL(ALIST,' ') !=' ' THEN
ALIST := ALIST || ';';
END IF;
ALIST := ALIST || REC.HOUR || '=' || REC.MINS;
END LOOP;
RETURN ALIST;
END;
/
and then
SELECT d.dow,
CONCAT_MYSTATS(d.dow)
FROM (SELECT distinct dow FROM mystats) d
SQLSERVER Version
CREATE FUNCTION CONCAT_MYSTATS (@pDOW INTEGER)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @ALIST VARCHAR(200);
SET @ALIST='';
DECLARE @QHR INTEGER;
DECLARE @QMINS INTEGER;
--
DECLARE cREC CURSOR FOR
SELECT h.HR AS HOUR,
SUM(ISNULL(d.mins,0)) AS MINS
FROM (SELECT 0 AS Hr
UNION ALL SELECT 1 AS HR
UNION ALL SELECT 2 AS HR
UNION ALL SELECT 3 AS HR
UNION ALL SELECT 4 AS HR
UNION ALL SELECT 5 AS HR
UNION ALL SELECT 6 AS HR
UNION ALL SELECT 7 AS HR
UNION ALL SELECT 8 AS HR
UNION ALL SELECT 9 AS HR
UNION ALL SELECT 10 AS HR
UNION ALL SELECT 11 AS HR
UNION ALL SELECT 12 AS HR
UNION ALL SELECT 13 AS HR
UNION ALL SELECT 14 AS HR
UNION ALL SELECT 15 AS HR
UNION ALL SELECT 16 AS HR
UNION ALL SELECT 17 AS HR
UNION ALL SELECT 18 AS HR
UNION ALL SELECT 19 AS HR
UNION ALL SELECT 20 AS HR
UNION ALL SELECT 21 AS HR
UNION ALL SELECT 22 AS HR
UNION ALL SELECT 23 AS HR) h
LEFT OUTER JOIN mystats d
ON d.hour = h.HR
WHERE d.dow = @pDOW
GROUP BY h.HR
ORDER BY 1;
--
OPEN cREC
FETCH cREC INTO @QHR,@QMINS;
WHILE @@Fetch_Status = 0
BEGIN
--
IF @ALIST!='' SET @ALIST=@ALIST + ';';
SET @ALIST=@ALIST + CAST(@QHR AS VARCHAR) + '=' + CAST(@QMINS AS VARCHAR);
--
FETCH cREC INTO @QHR,@QMINS;
END
CLOSE cREC
DEALLOCATE cREC
RETURN @ALIST;
END
and then
SELECT d.dow,
dbo.CONCAT_MYSTATS(d.dow)
FROM (SELECT distinct dow FROM mystats) d
and finally, for SQLITE all you need to do is
SELECT d.dow,
GROUP_CONCAT(d.hour || '=' || d.mins,';')
FROM mystats d
GROUP BY d.dow
Working example (SQLITE) at http://www.datagloop.com/?ACTION=LOGIN&USERNAME=DATAGLOOP/SO_CONCAT
Upvotes: 0
Denis de Bernardy
Reputation: 78413
Try tossing in array_agg(), e.g.:
WITH
monthly_stats as (
SELECT duration,
extract( 'dow' from datetime ) as dow,
extract( 'hour' from datetime ) as hour
FROM "statistics"
WHERE datetime >= '2013-10-01'
AND datetime < '2013-10-01' + interval '1 month'
AND duration >= 300
),
daily_stats as (
SELECT dow, hour, sum(duration/60) as minutes
FROM monthly_stats
GROUP BY dow, hour
)
SELECT dow, array_agg(minutes ORDER BY hour)
FROM daily_stats
GROUP BY dow
ORDER BY dow
Upvotes: 1
Related Questions
- Filter a grouped by result in PostgresSQL
- postgres group by in result
- GROUP BY one column, then by another column
- group by on one column, but select multiple
- How to group by fields which have specific value in SQL?
- How can I GROUP BY a single column in SQL?
- group in postgreSQL
- Group result in SQL by custom condition
- Group by group of values
- results for each group