表中每天值的总和

My table:

我的表:

Dataid  date        register_type  read_value
77      2012-08-15  gen            20
77      2012-08-15  solar          48
77      2012-08-16  gen            39
77      2012-08-16  gen            22
80      2012-07-11  gen            11
80      2012-07-12  id             23
91      2012-02-01  id              4
91      2012-02-01  gen            59
91      2012-02-08  gen            18

I would like, for each day, to do the sum of the read_values for only the "gen" register_type. I basically want the query to return the following table:

我希望每天只对“gen”register_type进行read_values的求和。我基本上希望查询返回下表:

dataid  date        daily_value
77      2012-08-15  20.00
77      2012-08-16  61.00
80      2012-07-11  11.00
91      2012-02-01  59.00
91      2012-02-08  18.00

I tried the following query, but it does not work:

我尝试了下面的查询，但是它不起作用:

select 
    dataid, 
    date_trunc('day', timestamp_localtime) as truncated_day,
    substring(cast(date_trunc('day', timestamp_localtime) as text)
              from 1 for 10) as date,            
    sum(read_value) as daily_gen
where register_type like ‘%gen%’
from table
group by dataid, date_trunc('day', timestamp_localtime) 
order by dataid, truncated_day

How would I write this query?

如何编写这个查询?

2 个解决方案

#1

Works in Postgres:

在Postgres工作:

SELECT dataid, date, sum(read_value) AS daily_value
FROM   tbl
WHERE  register_type = 'gen'
GROUP  BY 1,2
ORDER  BY 1,2

Or is your column named date actually something else than a date?
If it's actually a timestamp, replace date in my query with date::date (casting the timestamp to date) and it should work.
(You shouldn't use reserved words like date as identifiers to begin with, even if Postgres allows it.)

或者你的列名为date的专栏实际上不是一个日期?如果它实际上是一个时间戳，那么将查询中的日期替换为date: date(将时间戳转换为date)，它应该可以工作。(即使Postgres允许，也不应该使用日期等保留词作为标识符。)

#2

In SQL Server, the following would work:

在SQL Server中，如下所示:

SELECT SUM(read_value) AS daily_value, dataid, date
FROM (SELECT *, CONVERT(VARCHAR(10),date,105) AS newdate FROM Table)
WHERE type = 'gen'
GROUP BY newdate

For MySQL, use this instead:

对于MySQL，请使用以下命令:

SELECT SUM(read_value) AS daily_value, dataid, date
FROM (SELECT *, DATE_FORMAT(newdate, '%d-%m-%Y') AS day FROM Table)
WHERE type = 'gen'
GROUP BY newdate

The subquery is required since GROUP BY doesn't support the usual aggregate functions. This, however, is needed only if date is a datetime. If its just a date, then there's no need to extract the just the date from it.

由于GROUP BY不支持通常的聚合函数，所以需要子查询。然而，只有在date是datetime时才需要这个参数。如果它只是一个日期，那么就没有必要提取它的日期。

#1