CODEWITHSUNDEEP
javamysqlsql
Lucas Silva
Lucas Silva

Reputation: 1411

Complicated query in mysql database

I have two Tables:

**user**
  idU - autoincrement
  name


**automobile**
  idA - autoincrement
  id_user
  value
  date - type: yyyy-mm-dd

Each user has many automobile A automobile has only one user

Currently i am using the following query for all users(5 in total):

select sum(value) as user_value, name 
from user inner join automobile on user.idU = automobile.id_user 
where name like ? AND date = ?

what i want? I want the total value of all user in specific date using 1 query. how can i do this?

@edit1:

TABLE USER
--idU------name----
   1      mateus
   2      joao
   3      maria

TABLE AUTOMOBILE
--idA---id_user----value-------date------
   1       1         250     2013-10-13
   2       3         322     2013-10-13
   3       1         150     2013-10-13
   4       3          80     2013-10-13
   5       2         100     2013-11-13
   6       3         500     2013-11-13
   7       1        1100     2013-11-13
   8       1          50     2013-12-13
   9       3         135     2013-12-13
   10      2          40     2013-12-13

I request a query with date = 2013-10-13 and it return:

---name---value
  mateus   400
   maria   422

Upvotes: 1

Views: 64

Answers (3)

J A
J A

Reputation: 1766

I am not entirely sure if I got it right. Since you're using aggregate function ('sum') it would normally return only one row containing sum of the values. But if you'd like to execute the query anyway, maybe this would help (not tested)..

UPDATED

SELECT
    SUM(automobile.value) as totalValue,
    user.name
FROM
    automobile
    INNER JOIN
        user 
        ON automobile.id_user = user.idu
WHERE
    automobile.date between '2013-10-13' and '2013-10-13' 
GROUP BY
    automobile.id_user

Upvotes: 1

Hackerman
Hackerman

Reputation: 12295

Something like:

select sum(value) as user_value, 
name inner join automobile on user.idU = automobile.id_user 
where date = ?
group by user.idU

-----------EDIT------------

select name, sum(value) as value 
from user inner join automobile on user.idU = automobile.id_user 
where date = "2013-10-13"
group by automobile.id_user

SQLFiddle here: http://sqlfiddle.com/#!2/8115c/4

Upvotes: 2

Timshel
Timshel

Reputation: 1783

Assuming you're wanting a list of each user with the total value of their automobiles with a particular date, you'll need to group by any user fields which you're selecting:

SELECT
    SUM(a.value) as user_value,
    u.name
FROM
    user u INNER JOIN
    automobile a on u.idU = a.id_user
WHERE
    a.date = ?
GROUP BY
    u.name

Upvotes: 1

Related Questions