CODEWITHSUNDEEP
mysqlsqldatabase
JBoy
JBoy

Reputation: 5735

Add column to MySQL result

I'm trying to add a column to a Mysql result using a subquery, Suppose i have a table 'Cars' and a table 'Cars usage' Table Cars has a column "serial_number" and the Cars usage table has a column "serial_number" as well: So i wanna generate a result with the car name as first column and car usage as second:

Table cars:
-model
-serial_number

Table carsusage
-date
-serial_number

What i would like to achieve would look like so:

Model | Usage count
--------------------
|bar   | 1500       |
|foo   | 700        |

Ideally i need a sub-query, that, while querying the cars table, spits a query to the Cars usage table counting:

SELECT model, serial_number AS sn,(SELECT COUNT(serial_number) FROM cars_usage WHERE serial_number=sn) FROM cars;

So basically i would like to use the serial_number AS sn as a local variable and add the result of the count operation as second column. Any advice? No that much experience with querying a db here.

Thx

Upvotes: 1

Views: 2131

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

If you want usage per serial number:

select model, serial_number, count(*) from cars inner join carsusage on cars.serial_number=carsusage.serial_number where cars.serial_number=$var group by 1,2

or if you want usage per model:

select model, count(*) from cars inner join carsusage on cars.serial_number=carsusage.serial_number where cars.serial_number=$var group by 1

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

Why not just try something like

SELECT model, 
    serial_number AS sn,
    (SELECT COUNT(serial_number) FROM cars_usage cu WHERE cu.serial_number=c.serial_number) 
FROM  cars c;

Where you provide aliases for the tables, and then prefix the fields with the aliases.

You could also try a LEFT JOIN (do be carefull of inner join, because if there a no usages, it will not return a 0 entry for that car).

select c.model, 
       c.serialnumber sn,
       count(cu.*) 
from   cars c LEFT join 
       carsusage cu on c.serial_number=cu.serial_number
GROUP BY  c.model, 
          c.serialnumber

Upvotes: 1

Related Questions