SQL: How to to SUM two values from different tables
select region,sum(number) total
from
(
select region,number
from cash_table
union all
select region,number
from cheque_table
) t
group by region
How to sum two columns of different tables in sql
In case not all books had a CD:
SELECT A.Books
, A.Book_ID
, A.Author
, B.CD_Name
, A.rate+COALESCE(B.Cd_price,0) AS TOTAL_PRICE
FROM BOOK A
LEFT JOIN CD B ON A.BOOK_ID = B.BOOK_ID
Question's author evidenced that "The table name is book not Books"
I used originally BOOKS as a "suggestion" because (usually) tables name are plural.
how to sum two columns from different tables MySQL
First use UNION ALL
to get all the rows from the 2 tables that you want and then aggregate:
SELECT session_id, SUM(points) AS total_points
FROM (
SELECT session_id, spent_points AS points
FROM session_details
WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy")
UNION ALL
SELECT session_id, price_points
FROM template_sales
WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy")
) t
GROUP BY session_id
Sum two counts from 2 different tables in sql
You need to use UNION ALL
and then apply COUNT
SELECT
gender as Gender,
COUNT(*) as Total
FROM
(
SELECT gender
FROM memberOne
UNION ALL
SELECT gender
FROM memberTwo
) group by gender
SQL How to use SUM and Group By with multiple tables?
Here is a solution based on your data. Issue with your query is that you were joining tables on a non-unique column resulting in Cartesian product.
Data
DROP TABLE IF EXISTS A;
CREATE TABLE A
(num int,
id int,
col1 int);
INSERT INTO A VALUES (1, 100, 0);
INSERT INTO A VALUES (2, 100, 1);
INSERT INTO A VALUES (3, 100, 0);
INSERT INTO A VALUES (1, 101, 1);
INSERT INTO A VALUES (2, 101, 1);
INSERT INTO A VALUES (3 , 101, 0);
DROP TABLE IF EXISTS B;
CREATE TABLE B
(idx int,
id int,
col2 int);
INSERT INTO B VALUES (1, 100, 20);
INSERT INTO B VALUES (2, 100, 20);
INSERT INTO B VALUES (3, 100, 20);
INSERT INTO B VALUES (4, 101, 100);
INSERT INTO B VALUES (5, 101, 100);
DROP TABLE IF EXISTS C;
CREATE TABLE C
(idx int,
id int,
col3 int);
INSERT INTO C VALUES (1, 100, 1);
INSERT INTO C VALUES (2, 100, 1);
INSERT INTO C VALUES (3, 100, 1);
INSERT INTO C VALUES (4, 101, 10);
INSERT INTO C VALUES (5, 101, 1);
Solution
SELECT a_sum.id, col1_sum, col2_sum, col3_sum
FROM (SELECT id, SUM(col1) AS col1_sum
FROM a
GROUP BY id ) a_sum
JOIN
(SELECT id, SUM(col2) AS col2_sum
FROM b
GROUP BY id ) b_sum
ON (a_sum.id = b_sum.id)
JOIN
(SELECT id, SUM(col3) AS col3_sum
FROM c
GROUP BY id ) c_sum
ON (a_sum.id = c_sum.id);
Result is as expected
Note: Do outer joins if an id doesnt have to be present in all three tables.
SQL select sum with multiple where from different tables
Original Answer
First off, your submitted SQL was a mess, things were completely out of the conventional order, I'm not sure it would execute as presented. That said, this should do what you want, assuming I understand your desired output correctly:
SELECT users.id, users.name, users.surname, ROUND(SUM(purchases.sum),2) AS totalSum
FROM purchases
LEFT JOIN users ON users.id = purchases.user_id
WHERE status='completed'
GROUP BY users.name, users.surname
ORDER BY users.id
The key aspect you were missing from your query was the GROUP BY
section, I added ORDER BY
for my own benefit on my fiddle, use it if you wish, or not.
Here's a fiddle I prepped for you prior to your posting of your sample data, the main answer has been given by others I see so I'm just posting this because I already did the work on it. http://sqlfiddle.com/#!9/8fa2d/6/0
Answer 2.0
Fiddle is acting up so this is untested but give it a try.
SELECT users.id, users.name, users.surname, ROUND(SUM(CASE WHEN purchases.status = 'completed' THEN purchases.sum ELSE 0 END),2) AS totalSum
FROM users
LEFT JOIN purchases ON users.id = purchases.user_id
GROUP BY users.id
ORDER BY users.id
Select the sum of values from 2 tables and group by common foreign key
Is this what you want?
select u.*,
(select sum(a.score) from a where a.id_user = u.id_user) as a_sum,
(select sum(b.score) from b where b.id_user = u.id_user) as b_sum
from user u;
If you want the total score, just add them together:
select u.*,
( (select COUNT(1) from a where a.id_user = u.id_user) +
(select coalesce(sum(b.score), 0) from b where b.id_user = u.id_user)
) as ab_sum
from user u;
Find sum() from two different tables and join them based on a condition?
You can use union all
and group by
. For all id
s:
select id, date, sum(amount), sum(amount_settled)
from ((select id, date, amount, null as amount_settled
from table1
) union all
(select id, date, null as amount, amount_settled
from table2
)
) t
group by id, date
order by date;
You can filter for a particular id
using a where
clause in the outer query.
Related Topics
How to Join on a Stored Procedure
Concatenate/Merge Array Values During Grouping/Aggregation
Sql: How to to Sum Two Values from Different Tables
How to Add Time to Datetime in SQL
Mapping to Varchar and Nvarchar in Hibernate
What Are the Main Differences Between Option(Optimize for Unknown) and Option(Recompile)
Is Possible to Reuse Subqueries
Store Multiple Elements in JSON Files in Aws Athena
Determine a Table's Primary Key Using Tsql
How to Schedule a SQL Job in Microsoft Azure SQL Database
SQL to Query Text in Access with an Apostrophe in It
Drop Default Constraint on a Column in Tsql