sql - 表中每天的值总和

Question

我的 table :

Dataid  date        register_type  read_value77      2012-08-15  gen            2077      2012-08-15  solar          4877      2012-08-16  gen            3977      2012-08-16  gen            2280      2012-07-11  gen            1180      2012-07-12  id             2391      2012-02-01  id              491      2012-02-01  gen            5991      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:

dataid  date        daily_value77      2012-08-15  20.0077      2012-08-16  61.0080      2012-07-11  11.0091      2012-02-01  59.0091      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

我将如何编写此查询？

Answer 1

在 Postgres 中工作:

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

或者您的名为 date 的列实际上不是日期？
如果它实际上是一个 timestamp，请将我的查询中的 date 替换为 date::date(将 timestamp 转换为 date) 它应该可以工作。
(你不应该使用 reserved words 像 date 作为标识符开始，即使 Postgres 允许它。)