Sql query with joins and group by

Question

I have 3 tables:

I want to get SUM of ConsultantBalance, Amount and TaxAmount GROUP BY ConsultantId. Expected result:

I want to get not too complicated sql query and avoid sub-queries if it's possible.

Sql queries to create structure:

CREATE TABLE IF NOT EXISTS `ConsultantBalance` (`id` int(11) NOT NULL AUTO_INCREMENT, `ConsultantID` int(11) DEFAULT NULL, `BalanceType` int(11) DEFAULT NULL, `ConsultantBalance` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB  AUTO_INCREMENT=6 ;
INSERT INTO `ConsultantBalance` (`id`, `ConsultantID`, `BalanceType`, `ConsultantBalance`) VALUES (1, 1, 1, 100.00), (2, 1, 2, 50.00), (3, 2, 1, 200.00), (4, 2, 2, 230.00), (5, 3, 1, 300.00);
CREATE TABLE IF NOT EXISTS `ConsultantCredit` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ConsultantID` int(11) DEFAULT NULL, `CreditType` char(10) DEFAULT NULL, `Amount` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 ;
INSERT INTO `consultantCredit` (`id`, `ConsultantID`, `CreditType`, `Amount`) VALUES (1, 1, 'Deposit', 23.00), (2, 1, 'Cash', 942.00), (3, 2, 'Deposit', 17.00), (4, 2, 'Cash', 932.00), (5, 3, 'Deposit', 125.00), (6, 3, 'Cash', 922.00);
CREATE TABLE IF NOT EXISTS `ConsultantTax` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ConsultantID` int(11) DEFAULT NULL, `TaxName` char(5) DEFAULT NULL, `TaxAmount` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 ;
INSERT INTO `ConsultantTax` (`id`, `ConsultantID`, `TaxName`, `TaxAmount`) VALUES (1, 1, 'GST', 7.00), (2, 1, 'HST', 10.00), (3, 2, 'GST', 17.00), (4, 2, 'HST', 150.00), (5, 3, 'GST', 37.00), (6, 3, 'HST', 140.00);

Tim Biegeleisen · Accepted Answer · 2016-01-15 09:44:58Z

1

The following query first aggregates the sums for each of the three tables, and then joins the three tables together to get the result set you want.

SELECT t1.ConsultantID, t1.sumConsultantBalance, t2.sumAmount, t3.sumTaxAmount
FROM
(
    SELECT ConsultantID, SUM(ConsultantBalance) AS sumConsultantBalance
    FROM ConsultantBalance
    GROUP BY ConsultantID
) t1
INNER JOIN
(
    SELECT ConsultantID, SUM(Amount) AS sumAmount
    FROM ConsultantCredit
    GROUP BY ConsultantID
) t2
ON t1.ConsultantID = t2.ConsultantID
INNER JOIN
(
    SELECT ConsultantID, SUM(TaxAmount) AS sumTaxAmount
    FROM ConsultantTax
    GROUP BY ConsultantID
) t3
ON t2.ConsultantID = t3.ConsultantID

answered Jan 15, 2016 at 9:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Respant · Accepted Answer · 2016-01-15 09:54:56Z

My sql query looks a quite big and complicated but it works

SELECT 
ConsultantBalance1.ConsultantId, 
(
    SELECT SUM( ConsultantBalance2.ConsultantBalance ) 
    FROM ConsultantBalance AS ConsultantBalance2
    WHERE ConsultantBalance2.ConsultantId = ConsultantBalance1.ConsultantId
) AS sumConsultantBalance, 
(
    SELECT SUM( ConsultantCredit2.Amount ) 
    FROM ConsultantCredit AS ConsultantCredit2
    WHERE ConsultantCredit2.ConsultantId = ConsultantBalance1.ConsultantId
) AS sumAmount,
(
    SELECT SUM( ConsultantTax2.TaxAmount ) 
    FROM ConsultantTax AS ConsultantTax2
    WHERE ConsultantTax2.ConsultantId = ConsultantBalance1.ConsultantId
) AS sumTaxAmount
FROM ConsultantBalance AS ConsultantBalance1
INNER JOIN ConsultantCredit AS ConsultantCredit1 ON ( ConsultantBalance1.ConsultantId = ConsultantCredit1.ConsultantId ) 
INNER JOIN ConsultantTax AS ConsultantTax1 ON ( ConsultantBalance1.ConsultantId = ConsultantTax1.ConsultantId ) 
GROUP BY ConsultantBalance1.ConsultantId

Kickstart · Accepted Answer · 2016-01-15 10:27:41Z

0

Similar to the solution by Tim Biegeleisen, but with one less sub query. In MySQL sub query joins make poor use of indexes. This solution avoids 1 sub query:-

SELECT cc.ConsultantID, sub0.sumConsultantBalance, sub1.sumTaxAmount, SUM(cc.Amount) AS sumAmount
FROM ConsultantCredit cc
(
    SELECT ConsultantID, SUM(ConsultantBalance) AS sumConsultantBalance
    FROM ConsultantBalance
    GROUP BY ConsultantID
) sub0
ON cc.ConsultantID = sub0.ConsultantID
INNER JOIN
(
    SELECT ConsultantID, SUM(TaxAmount) AS sumTaxAmount
    FROM ConsultantTax
    GROUP BY ConsultantID
) sub1
ON cc.ConsultantID = sub1.ConsultantID
GROUP BY cc.ConsultantID, sub0.sumConsultantBalance, sub1.sumTaxAmount

answered Jan 15, 2016 at 10:27

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

Sql query with joins and group by

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related