Mysql Query grouping and having clause

[Noorain]

Dear Sir

Please help me
i want a query which out put is:

Vendor                                Amount
Computer
AA                                       52000
BB                                       20000
Total:                                   72000
Printer
XX                                       12000
YY                                      18000
Total:                                  30000

Thanks

[mwasif]

How your database look like?

[Atli]

Hey.

MySQL returns a two-dimensional list of data. What you posted is three-dimensional. You should generally avoid making MySQL jump through hoops to format the data in such a way, but rather leave that to your front-end code.

Having said that, check out the WITH ROLLUP modifier for the GROUP BY clause. It can give you a 2D representation of your 3D results. Not exactly what you posted, but close enough.

[Noorain]

I used 3 tables for this query. Please help me

CREATE TABLE `item` (
  `id` int(11) NOT NULL auto_increment,
  `item_name` varchar(3) collate latin1_general_ci NOT NULL,
PRIMARY KEY  (`id`)
) 
 
INSERT INTO `item` VALUES (1, 'Computer');
INSERT INTO `item` VALUES (2, 'Printer');

CREATE TABLE `vndr` (
  `id` int(11) NOT NULL auto_increment,
  `vndr_name` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`)
) 
 
INSERT INTO `vndr` VALUES (1, 'AA');
INSERT INTO `vndr` VALUES (2, 'BB');
INSERT INTO `vndr` VALUES (3, 'EE');
INSERT INTO `vndr` VALUES (4, RR');
INSERT INTO `vndr` VALUES (5, 'WW');

CREATE TABLE `vt` (
  `id` int(11) NOT NULL auto_increment,
  `item_id` int(11) NOT NULL,
  `vndr_id` int(11) NOT NULL,
  `amount` double(10,2) NOT NULL,
  `remarks` varchar(255) collate latin1_general_ci NOT NULL,
PRIMARY KEY  (`id`)
)
 
INSERT INTO `vt` VALUES (1, 1, 1,  25000.00, '');
INSERT INTO `vt` VALUES (2, 1,1,25000.00, '');
INSERT INTO `vt` VALUES ( 3, 1, 2, 25000.00, '');
INSERT INTO `vt` VALUES (4, 2,4,  12000.00, '');
INSERT INTO `vt` VALUES (5, 2, 3,  18000.00, '');
INSERT INTO `vt` VALUES (6, 2, 3,  19000.00, '');

Thanks