How to Fixed MySql Query Fixed. [Resolved]

Posted by Jayakumars under Sql Server on 8/3/2016 | Points: 10 | Views : 151 | Status : [Member] [MVP] | Replies : 1
Hi

I need MySQl Query issue.

This is table Query

CREATE  TABLE `test`.`mas` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR(45) NULL ,

`amount1` DECIMAL(18,2) NULL ,

PRIMARY KEY (`id`) );



CREATE  TABLE `test`.`trans` (

`id` INT NOT NULL AUTO_INCREMENT ,

`Receipt` DECIMAL(18,2) NULL ,

`Payment` DECIMAL(18,2) NULL ,

`nameid` INT NULL ,

`name` VARCHAR(45) NULL ,

PRIMARY KEY (`id`) );



INSERT INTO `test`.`mas` (`name`, `amount1`) VALUES ('John', 2000);

INSERT INTO `test`.`mas` (`name`, `amount1`) VALUES ('wesley', 4000);

INSERT INTO `test`.`mas` (`name`, `amount1`) VALUES ('Kilbert', 8000);



INSERT INTO `test`.`trans` (`Receipt`, `Payment`, `nameid`, `name`) VALUES (100, 200, 1, 'John');

INSERT INTO `test`.`trans` (`Receipt`, `Payment`, `nameid`, `name`) VALUES (400, 200, 1, 'John');

INSERT INTO `test`.`trans` (`Receipt`, `Payment`, `nameid`, `name`) VALUES (300, 10, 2, 'Wesley');



when i write the query for left join


SELECT a.id,a.name,amount1,Receipt,payment,nameid,b.name 
FROM test.mas as a left join test.trans as b on a.id=b.nameid;


now working fine return all records match or not coming correctly.

But when i use sum aggregation i did not receive correct output.
when i use sum here 1 record only shows here but this is wrong any one fixed this



this is my query but this is wrong one record only show this

SELECT a.id,a.name,sum(amount1)as amount1,sum(Receipt) as Receipt,sum(payment) as payment,
sum(amount1)+sum(Receipt)-sum(payment) as Total,nameid,b.name
FROM test.mas as a left join test.trans as b on a.id=b.nameid;



which is wrong in this query any one alter and post here.

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 8/3/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try this

SELECT a.id,a.name,sum(amount1)as amount1,sum(Receipt) as Receipt,sum(payment) as payment,sum(amount1)+sum(Receipt)-sum(payment) as Total,nameid,b.name
FROM test.mas as a left join test.trans as b on a.id=b.nameid
GROUP BY a.id,a.name,nameid,b.name


The problem was you was missing Group By

Hope that helps

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response