MySQL, two tables displaying information from both tables

Question

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?

and what is the related key bet the tables ?

Abhik Chakraborty
– Abhik Chakraborty

2014-05-15 07:37:10 +00:00
Commented May 15, 2014 at 7:37 — Abhik Chakraborty
– Abhik Chakraborty, Commented May 15, 2014 at 7:37

Ravi Dhoriya ツ · Accepted Answer · 2014-05-15 07:44:48Z

2

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;

edited May 15, 2014 at 7:44

answered May 15, 2014 at 7:37

Ravi Dhoriya ツ

4,4148 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

said · Accepted Answer · 2014-05-15 07:46:48Z

1

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

answered May 15, 2014 at 7:46

said

5535 silver badges12 bronze badges

Comments

Abhik Chakraborty · Accepted Answer · 2014-05-15 07:47:43Z

1

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

answered May 15, 2014 at 7:47

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Collectives™ on Stack Overflow

MySQL, two tables displaying information from both tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related