You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    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/


Sign In to post your comments