Board index » delphi » SQL to "group by month" -> date part

SQL to "group by month" -> date part

Hi there!
I need to build a report with the table below:

ESTAT_PROTOCOL:
Column Name    Type      Length Nulls Defaults
date_protoc       date                   no      no
qtd_item1          integer         2    yes    null
qtd_item2          integer         2    yes    null
qtd_item3          integer         2    yes    null

Sample data:
Date Protoc   |  Item1   |  Item2   |  Item3   |  Item4
-----------------------------------------------------
02/01/2001    |         2   |         1   |        10  |       2
03/01/2001    |         1   |         4   |         2  |      13
04/01/2001    |         1   |         3   |         8  |        8
04/07/2001    |         3   |        13   |       48  |       4
04/07/2001    |         3   |         8   |         7  |       10

Where the only primary key is "date_protoc". The problem is that I need
the data grouped by "month", not "regular date". Then I'd sum the other
field values into one. The report might look like:

Date Protoc   |  Item1   |  Item2   |  Item3   |  Item4
-----------------------------------------------------
Jan/2001       |         4   |         8   |        20  |      23
Feb/2001      |        11  |        23  |        5    |      74
Mar/2001      |       32  |        11  |        41  |      57
Jul/2001        |         6   |        21  |        55  |      14

And I couldnt find an apropriate SQL statement and function to develop
this. I'm using database CA-Ingres, Delphi5 and QuickReport.
I think that it'll be something like this:

SELECT date_protoc, SUM(qtd_item1), SUM(qtd_item2), SUM(qtd_item3)
FROM estat_protocol
GROUP BY date_part('month', date_protoc)

Thanks in advance,

Rodrigo Hjort
rodrigo_hj...@yahoo.com

 

Re:SQL to "group by month" -> date part


Rodrigo

Quote
> Where the only primary key is "date_protoc".

That won't work with the data you supplied.  The date is not unique...so it
can't serve as a primary key.  Assuming the last date entry was a typo
(otherwise you would need another column to make it unique), you might try
this:

CREATE TABLE EstatProtocol (
  date_protoc DATETIME NOT NULL PRIMARY KEY,
  item1 INT NOT NULL,
  item2 INT NOT NULL,
  item3 INT NOT NULL,
  item4 INT NOT NULL);

INSERT EstatProtocol VALUES('02/01/2001', 2, 1, 10, 2);
INSERT EstatProtocol VALUES('03/01/2001', 1, 4, 2, 13);
INSERT EstatProtocol VALUES('04/01/2001', 1, 3, 8, 8);
INSERT EstatProtocol VALUES('04/07/2001', 3, 13, 48, 4);
-- Changed the following date to 4/8
INSERT EstatProtocol VALUES('04/08/2001', 3, 8, 7, 10);

SELECT
    CASE MONTH(date_protoc)
      WHEN 1 THEN 'Jan'
      WHEN 2 THEN 'Feb'
      WHEN 3 THEN 'Mar'
      WHEN 4 THEN 'Apr'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'Jun'
      WHEN 7 THEN 'Jul'
      WHEN 8 THEN 'Aug'
      WHEN 9 THEN 'Sep'
      WHEN 10 THEN 'Oct'
      WHEN 11 THEN 'Nov'
      WHEN 12 THEN 'Dec'
    END AS catg_month,
    YEAR(date_protoc) AS catg_year,
    SUM(item1) AS item1_qty,
    SUM(item2) AS item2_qty,
    SUM(item3) AS item3_qty,
    SUM(item4) AS item4_qty
  FROM EstatProtocol
GROUP BY YEAR(date_protoc), MONTH(date_protoc);

...which would give you the following:

catg_month catg_year   item1_qty   item2_qty   item3_qty   item4_qty
---------- ----------- ----------- ----------- ----------- -----------
Feb        2001        2           1           10          2
Mar        2001        1           4           2           13
Apr        2001        7           24          63          22

(3 row(s) affected)

If you make this a view, you can then format the date as you wish.

Joe

Re:SQL to "group by month" -> date part


Joe,

Thaks for answering me! Well, I got it!
Actually, I created a view with the SQL below:

"create view estat_protocolo_view as
  select cod_orgao_protoc,
    dat_protoc,
    mes_protoc = date_part('month', dat_protoc),
    ano_protoc = date_part('year', dat_protoc),
    qtd_petica_inicia, qtd_petica_geral_forum,
    qtd_petica_geral_integr, qtd_petica_geral_trf,
    qtd_petica_admini, qtd_certid_distri_geral,
    qtd_certid_distri_solici, qtd_certid_distri_eleitr,
    qtd_certid_cartor_objeto_pe, qtd_certid_cartor_homoni,
    qtd_certid_cartor_inteir_teor
  from estat_protocolo"

And then used the instruction:

"select min(dat_protoc) as dat_primei,
  mes_protoc, ano_protoc,
  sum(qtd_petica_inicia) as tot_petica_inicia,
  sum(qtd_petica_geral_forum) as tot_petica_geral_forum,
  sum(qtd_petica_geral_integr) as tot_petica_geral_integr,
  sum(qtd_petica_geral_trf) as tot_petica_geral_trf,
  sum(qtd_petica_admini) as tot_petica_admini
from estat_protocolo_view
where (cod_orgao_protoc = :p_cod_orgao_protoc)
  and (dat_protoc between :p_data_inicial and :p_data_final)
group by ano_protoc, mes_protoc"

And this solved my problem. Ah, CA-Ingres does not support
the functions MONTH() or YEAR().

Rodrigo Hjort
rodrigo_hj...@yahoo.com

Re:SQL to "group by month" -> date part


Rodrigo

Quote
> Ah, CA-Ingres does not support
> the functions MONTH() or YEAR().

Yeah...sorry about that.  It is easy to get the dialects confused.  (Not all
DATE_PART() implementations are the same either.  E.g., in SQL Server it is
DATEPART().  What a mess...)

Joe

Other Threads