get sum of one column based on another column of other table

[drogo96]

I have to calculate the sum of the amount of the transaction table which has columns of distinct phones, dates and amounts. These numbers are also included in the Users table which has columns for unique phone, date and is_mobile. Is_mobile may have 0 or 1 value.

for a better understanding of my question, I am mentioning the MySQL query

Select count(phone) from transaction where date(dateCreated)>='2022-10-10' and date(dateCreated)<='2022-10-31' and phone IN(Select phone from users where is_mobile=0)

[drogo96]

I used count(distinct) on phone column of transaction.Then i applied a filter using is_mobile column of users.Is this will be a true way ?Because for rigth now my results are correct.I dont want this to effect later 

[Adamboer]

Based on your MySQL query, it seems like you are trying to count the number of distinct phone numbers in the "transaction" table that meet certain date criteria, and also appear in the "users" table with the "is_mobile" column set to 0.

If your goal is to calculate the sum of the amounts instead of just counting the distinct phone numbers, you can modify your query to use the SUM() function and join the two tables on the phone number and date fields. Here is an example query:

SELECT SUM(amount) FROM transaction t INNER JOIN users u ON t.phone = u.phone AND DATE(t.dateCreated) = DATE(u.date) WHERE DATE(t.dateCreated) BETWEEN '2022-10-10' AND '2022-10-31' AND u.is_mobile = 0;

This query joins the "transaction" and "users" tables on the "phone" and "date" fields, and then sums up the "amount" field in the "transaction" table for all matching records where the "is_mobile" field in the "users" table is 0 and the "dateCreated" field in the "transaction" table falls within the specified date range.