locked
Amount result in SQL query RRS feed

  • Question

  • User639567535 posted

    When I try this query

    Select S.Name,S.No,
    SUM(Case when s.Furit =’Mango then total else 0 end) as Mango,
    SUM(Case when s.Furit =’Apple then total else 0 end) as Apple,
    SUM(total) total, 
    Sum(convert(int,Am)) Amount, MAX(S.Value) Value
     from (
    Select
     Veh_table.Name, Veh_table.No, VV_table.Furit, count(VV_table.Furit) as total,  Veh_table. Amount as Am, Veh_table. Value
    from VV_table 
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE 
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, VV_table.Furit,Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name

    this show result like this

    Name    No  Mango   Apple   total   Amount      Value
    John    431   9       2       11       964      98

    When I remove Fruit from above and try this

    Select S.Name,S.No
    SUM(total) total, 
    Sum(convert(int,Am)) Amount, MAX(S.Value) Value
     from (
    Select
     Veh_table.Name, Veh_table.No,count(VV_table.Furit) as total,  Veh_table.Amount as Am, Veh_table.Value
    from VV_table 
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE 
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name
    
    Name    RegNo   total  Amount   Value
    John    431       11      243   98

    Now I want also Mango and Apple in data also correct amount is 243 . when I remove VV_table.Furit from select then amount 243 is display which is correct whereas when I add VV_table.Furit then amount 964 is display which is wrong I want result like this

    Name    No  Mango   Apple   total   Amount  Value
    John    431     9   2        11     243      98
    Friday, September 16, 2016 7:48 AM

All replies

  • User283571144 posted

    Hi Bakhtawar,

    Now I want also Mango and Apple in data also correct amount is 243 . when I remove VV_table.Furit from select then amount 243 is display which is correct whereas when I add VV_table.Furit then amount 964 is display which is wrong I want result like this

    According to your codes and description, we couldn't reproduce your issue.

    So you need to solve it by yourself.

    I suggest you could try to use inner join again during the query.

    For example:

    Table1:

    Select S.Name,S.No,
    SUM(Case when s.Furit =’Mango’ then total else 0 end) as Mango,
    SUM(Case when s.Furit =’Apple’ then total else 0 end) as Apple,
    SUM(total) total,
    Sum(convert(int,Am)) Amount, MAX(S.Value) Value
     from (
    Select
     Veh_table.Name, Veh_table.No, VV_table.Furit, count(VV_table.Furit) as total,  Veh_table. Amount as Am, Veh_table. Value
    from VV_table
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, VV_table.Furit,Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name

    Result::

    Name    No  Mango   Apple   total    Value
    John    431   9       2       11      98

    Inner join Table2 on Table1.name = Table2.name:

    Select S.Name,S.No
    SUM(total) total, 
    Sum(convert(int,Am)) Amount, MAX(S.Value) Value
     from (
    Select
     Veh_table.Name, Veh_table.No,count(VV_table.Furit) as total,  Veh_table.Amount as Am, Veh_table.Value
    from VV_table 
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE 
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name

    Result:

    Name    Amount   
    John      243   

    Total:

    Select Table1.Name ,Table1.RegNo,Table1.Mango,Table1.Apple,Table1.total,Table1.Value,Table2.Amount 
    from
    (
    Select S.Name as Name,S.No as RegNo,
    SUM(Case when s.Furit =’Mango’ then total else 0 end) as Mango,
    SUM(Case when s.Furit =’Apple’ then total else 0 end) as Apple,
    SUM(total) total,
    Sum(convert(int,Am)) Amount, MAX(S.Value) Value
     from (
    Select
     Veh_table.Name, Veh_table.No, VV_table.Furit, count(VV_table.Furit) as total,  Veh_table. Amount as Am, Veh_table. Value
    from VV_table
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, VV_table.Furit,Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name
    ) Table1
    inner join
    (
    Select S.Name,
    Sum(convert(int,Am)) Amount
     from (
    Select
     Veh_table.Name, Veh_table.No,count(VV_table.Furit) as total,  Veh_table.Amount as Am, Veh_table.Value
    from VV_table 
    inner join Veh_table on VV_table.MID=Veh_table.ID
    inner join Re_table on Veh_table.RID=Re_table.RID
    WHERE 
    Re_table.StartDate>= '2016-08-01 00:00:00' and
    Re_table.EndDate<='2016-08-31 23:59:59'   and
    Re_table.Region= 'UK'
    and Veh_table.No= '431'
    AND Furit <> ''
    Group By Veh_table.Name, Veh_table.RegNo, Veh_table.Amount,Veh_table.Value) S
    GROUP BY
    s.No,s.Name
    )  Table2
    on Table1.Name = Table2.Name

    Note: I just write some idea, you need to test it in your sql server query.

    Best Regards,

    Brando

    Friday, September 16, 2016 9:02 AM