Category: SQL Server

02 Aug 2016 21:22

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	5

How to Fixed in MySQL Issue.

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.

#767446

02 Aug 2016 23:04

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

Can you provide some data of the records from your database. And also what are the result value which you are getting using your query. So that we can easily identify the issue.
Did you try any query? Can you post the query which you try?. Because I do not familiar with my sql. If you post your query. I can try to correct the logic.

By Nathan
Direction is important than speed

#767510

09 Aug 2016 06:15

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	5

Hi,

I didn't test your query clearly but as per your query when you use Aggregate functions you must and should use the rest of the columns in grouping clause.


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

Hi,

As per your previous post I suspect that issue might be group clause in your select query please find the details below for more


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

Hope this will helpful to you...

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

How to Fixed in MySQL Issue.

About dotnetspider.com

Quick Links

General

Services