CODEWITHSUNDEEP
mysqlsql
MrSilent
MrSilent

Reputation: 574

MySQL, two tables displaying information from both tables

I am learning SQL currently and I have a question. I have two tables called "vehicul" and proprietate. The first table (vehicul) looks like this:

   id   nr_vehicul  marca   id_marca    tip     culoare     capacitate_cilindrica
    1   DJ-01-AAA   Mercedes    1       CLK 350 negru       3500
    2   DJ-01-BBB   Mercedes    1       S 500   silver      5000
    3   DJ-01-CCC   Mercedes    1       ML 550  alb         5500
    4   DJ-01-DDD   BMW         2       325     galben      2500
    5   DJ-01-EEE   BMW         2       X5      negru       350

And the second table (proprietate) looks like this:

id serie_buletin    cnp        nr_vehicul data_cumpararii pret
1   AK162332    2006036035087   DJ03AAA     2014-05-01  35000
2   AK162332    2006036035087   DJ03BBB     2014-05-02  90000
3   AK176233    6548751520125   DJ03CCC     2014-05-03  55000
4   BZ257743    6548751520125   DJ03DDD     2014-05-04  25000
5   BZ257743    2006036035087   DJ03EEE     2014-05-05  63000

I want to display the column "marca" from the first table and the column "price" from the second, but like this. 

 marca   |  pret
Mercedes | 180000
  BMW    |  88000

Basically I have three Mercedes cars and two BMW cars, how can I display Mercedes one time and the prices of those three summed up?

Upvotes: 1

Views: 55

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Your tables are not related and hence its impossible to get the desired output as you are looking for.

For the 2nd table I would suggest to add a column called id_marca and store the value of each id_marca from first table to the 2nd table. So for all Mercedes its 1 and in the 2nd table also store it as 1 and for BMW its 2 and so on.

Now your tables are related and you can join as

select
v.marca
sum(p.pret) as pret
from vehicul v
join proprietate p on p.id_marca = v.id_marca
group by marca

Upvotes: 1

said
said

Reputation: 553

use this requeste 

select marca, sum(pret)
from vehicul as Ve, proprietate as Pr
where Ve.nr_vehicul=PR.nr_vehicul
group by marca

or 

select marca, sum(pret)
    from vehicul as Ve
    where Ve.id in(select nr_vehicul from  proprietate)
    group by marca

Upvotes: 1

Ravi Dhoriya ツ
Ravi Dhoriya ツ

Reputation: 4414

You need to join two tables and GROUP them based on marca field and sum pret

select marca, sum(pret)
from table1 as t1, table2 as t2
where t1.id=t2.id
group by marca

Here I'm assuming that id field is joining two tables, (but as I can see from your sampel data it isn't relating to each-other actually)

EDIT

I think you are missing id_marca field in table2. If its there then it would join to that column as below example:

select marca, sum(pret)
from table1 as t1, table2 as t2
where t1.id_marca=t2.id_marca
group by id_marca;

Upvotes: 2

Related Questions