CODEWITHSUNDEEP
phpmysqlsql
GabAntonelli
GabAntonelli

Reputation: 767

add a column to a mysql query from other subqueries

i have a table user(id,name) and a table payments(date,amount,user_id)

I need a query to display for each user the total sum of payments and the payments in last month.

User - totalpayments - monthlypayments

something like this

  1. SELECT id, name from user;
  2. SELECT COUNT(amount) FROM payments WHERE user_id=user.id;
  3. SELECT COUNT(amount) FROM payments WHERE user_id=user.id AND (data<=$timestamptoday AND data>=$timestamp1stday)

is it possible to do that without doing many queries?

Upvotes: 2

Views: 383

Answers (2)

DiMono
DiMono

Reputation: 3368

Absolutely you can do this as a single query:

SELECT    user.name, SUM(amount) AS totalpayments, SUM(IF(date <= '$enddate' AND date >= '$startdate'),amount,0) AS monthlypayments
FROM      user LEFT JOIN payments ON user.id = payments.user_id
GROUP BY  user.id
ORDER BY  user.name ASC

Note that I'm using SUM rather than COUNT on the assumption that you're looking for the total amounts of the payments, rather than the number of payments there were.

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

Sure you can do it within one query. 

SELECT
    u.ID,
    u.Name,
    SUM(p.amount),
    SUM(CASE
          WHEN p.date <= '2013-02-01' AND p.date >= '2013-01-01' THEN p.amount
          ELSE 0
        END)
FROM
    Users u
JOIN
    Payments p ON u.ID = p.UserID
GROUP BY
    u.ID,
    u.Name

Working DEMO

Upvotes: 2

Related Questions