sql - MySQL. Average price, connecting two databases -

i learning mysql , not understand how something. have 2 tables , want display stuff out of it, it's pretty hard explain i'd rather show you. tables:

create table if not exists `proprietate` (   `id` int(11) not null auto_increment,   `serie_buletin` varchar(8) not null,   `cnp` bigint(20) not null,   `nr_vehicul` int(11) not null,   `data_cumpararii` date not null,   `pret` int(11) not null,   `id_persoana` int(11) not null,   primary key (id), ) engine=innodb  default charset=latin1 auto_increment=16 ;   create table if not exists `vehicul` (   `id` int(11) not null auto_increment,   `nr_vehicul` int(11) not null,   `marca` varchar(30) not null,   `id_marca` int(11) not null,   `tip` varchar(15) not null,   `culoare` varchar(15) not null,   `capacitate_cilindrica` int(11) not null,   `id_proprietate` int(11) not null,   primary key (id),   foreign key (id_proprietate) references proprietate(id) ) engine=innodb  default charset=latin1 auto_increment=16 ; 

and these values inside tables:

insert `proprietate` (`id`, `serie_buletin`, `cnp`, `nr_vehicul`, `data_cumpararii`, `pret`, `id_persoana`) values (1, 'ak162332', 2006036035087, 4, '2014-05-01', 35000, 1), (2, 'ak162332', 2006036035087, 10, '2014-05-02', 90000, 2), (3, 'ak176233', 6548751520125, 2, '2014-05-03', 55000, 3), (4, 'bz257743', 6548751520125, 2, '2014-05-04', 25000, 4), (5, 'bz257743', 2006036035087, 15, '2014-05-05', 63000, 5), (6, 'dc456542', 2003564784513, 7, '2014-05-06', 30000, 6), (7, 'en654872', 2012654879521, 6, '2014-05-07', 50000, 7);  insert `vehicul` (`id`, `nr_vehicul`, `marca`, `id_marca`, `tip`, `culoare`, `capacitate_cilindrica`, `id_proprietate`) values (1, 4, 'mercedes', 1, 'clk 350', 'negru', 3500, 1), (2, 10, 'mercedes', 1, 's 500', 'silver', 5000, 2), (3, 2, 'mercedes', 1, 'ml 550', 'alb', 5500, 3), (4, 2, 'bmw', 2, '325', 'galben', 2500, 4), (5, 15, 'bmw', 2, 'x5', 'negru', 3500, 5), (6, 7, 'audi', 3, 'r5', 'mov', 5000, 6), (7, 6, 'audi', 3, 'q5', 'metalic', 3000, 7); 

what want display is:

 marca    |  nr_vehicul   |  average_price   audi    |      13       |     40000   bmw     |      17       |     44000 mercedes  |      16       |     60000 

how can that? far have managed display first 2 columns have no idea how reference first table in second , calculate average price. have far:

select marca, sum(nr_vehicul) nr_vehicul vehicul group marca 

can me please?

you should join tables combined information both of them:

select marca, sum(vehicul.nr_vehicul) nr_vehicul, avg(pret) pret vehicul      left outer join proprietate on (id_proprietate = proprietate.id) group marca; 

see this sql fiddle session output.

first select data (column names appropriate functions used) need: marca, sum(vehicul.nr_vehicul), avg(pret), construct joined structure mysql should retrieve these informations: vehicul, proprietate.

for structure need vehicul table, group result set. want join proprietate table vehicul table properly, make sure correct data structure created. since have foreign key 1 table other, easiest way use key: left outer join proprietate on (id_proprietate = proprietate.id).

for more information on understanding different join types, please see article craig buckler.