complex query

jweissig

Hi,

I need some help with a complex query. here is my database design and some example data.

CREATE TABLE io_ap (
id int(4) NOT NULL default '0',
type int(2) NOT NULL default '0',
name char(30) NOT NULL default '0',
money decimal(9,2) NOT NULL default '00.00',
date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
);

INSERT INTO io_ap VALUES (1, 1, 'Stuart Co', '5400000.00', '2002-08-01 00:00:00');
INSERT INTO io_ap VALUES (2, 1, 'Stuart Co', '1500000.00', '2002-08-01 00:00:00');
INSERT INTO io_ap VALUES (3, 1, 'Smit Fuel', '1000000.00', '2002-08-30 00:00:00');
INSERT INTO io_ap VALUES (4, 1, 'Toyota', '300000.00', '2002-09-01 00:00:00');
INSERT INTO io_ap VALUES (5, 1, 'ModSys', '300000.00', '2002-09-30 00:00:00');
INSERT INTO io_ap VALUES (6, 2, 'MPower', '100000.00', '2002-11-30 00:00:00');
INSERT INTO io_ap VALUES (7, 2, '3Dp', '100000.00', '2002-11-30 00:00:00');
INSERT INTO io_ap VALUES (8, 2, 'Johnson', '500000.00', '2002-12-01 00:00:00');
INSERT INTO io_ap VALUES (9, 2, '3Dp', '500000.00', '2002-12-01 00:00:00');
INSERT INTO io_ap VALUES (10, 2, 'rPlug', '150000.00', '2002-12-30 00:00:00');

What i would like to do is have something that will tell me the sum() of type 1 between 2002-01-01 and 2002-10-31 and the same for type 2. But i would like these values for each month of the year.

I have something like this:

select sum(money) as money_one, sum(money) as money_two from io_ap where date between date '2002-01-01' and date '2002-12-31' and type = '2';

can you please help me.
- Justin

Lars_Berg

select sum(money) as money_one,type,month(date) 
  from io_ap gr
 group by type, month(date)

month is not a standard function so you may have to change that depending on which DBMS.

jweissig

Hi,

Wow this is awesome.. i am using mysql this is perfect.. thanks dude. But how did you get multiple rox to show up? What does this "gr" do?

from io_ap "gr" group

Justin

jweissig

Hi,

I found it works awesome when i do this.

mysql> select sum(money) as money, type, date from io_ap group by type, month(date);
+-------------+------+---------------------+
| money | type | date |
+-------------+------+---------------------+
| 1200000.00 | 1 | 2003-01-30 00:00:00 |
| 70000.00 | 1 | 2003-02-01 00:00:00 |
| 3650800.00 | 1 | 2003-03-01 00:00:00 |
| 300000.00 | 1 | 2003-04-01 00:00:00 |
| 2943200.00 | 1 | 2003-05-01 00:00:00 |
| 3300000.00 | 1 | 2003-06-30 00:00:00 |
| 2900000.00 | 1 | 2003-07-01 00:00:00 |
| 8227000.00 | 1 | 2002-08-01 00:00:00 |
| 1781400.00 | 1 | 2002-09-01 00:00:00 |
| 2670000.00 | 1 | 2002-11-30 00:00:00 |
| 19240000.00 | 1 | 2002-12-01 00:00:00 |
| 3062000.00 | 2 | 2002-01-01 00:00:00 |
| 2357150.00 | 2 | 2003-03-30 00:00:00 |
| 335038.00 | 2 | 2003-04-01 00:00:00 |
| 305490.00 | 2 | 2002-07-01 00:00:00 |
| 1050000.00 | 2 | 2002-08-30 00:00:00 |
| 959050.00 | 2 | 2002-09-01 00:00:00 |
| 1929812.50 | 2 | 2002-10-15 00:00:00 |
| 2867555.00 | 2 | 2002-11-30 00:00:00 |
| 2848013.00 | 2 | 2002-12-30 00:00:00 |
+-------------+------+---------------------+
20 rows in set (0.01 sec)

Thanks for all your help dude.

Justin