Sql query in currect data by using inner join

ERPNext Customize ERPNext
, , ,
1

I NEED PROFESSIONAL ONE TO SOLVE THIS PROBLEM

WHY THE RESULT OF QTY INCORECT ?

res = frappe.db.sql(f"""SELECT

  w.custom_location_id as locationID,
 sii.warehouse as warehouse,
 sii.item_code as item_code,
 sii.qty,
 sli.actual_qty
 
  FROM (
  
       SELECT
       parent,
       item_group,
       warehouse as warehouse,
       item_code as item_code,
       SUM(qty) AS qty
       
  
  from `tabSales Invoice Item` 
  
  group by item_code,warehouse
  
  ) sii
  
  LEFT JOIN (
  
  SELECT 
       warehouse as warehouse,
       item_code as item_code,
       SUM(actual_qty) AS actual_qty
  
  FROM `tabStock Ledger Entry`
  
  
 group by item_code,warehouse
  
  ) sli
  
  on  sli.item_code = sii.item_code
  

INNER JOIN (

SELECT 
   name,
   status,
   posting_date,
   custom_business_type

from `tabSales Invoice` 

) si

  on sii.parent = si.name
  
  inner join  `tabWarehouse` w on w.name = sii.warehouse
  
    
  where 
    si.status != 'Cancelled'
    AND si.status != 'Draft'
   {sql_date_cond}
   
     group by  sii.warehouse,sii.item_code

   """, as_dict=True)


    
result = res
2

i tried this code but the result is true but i can’t see the data (actual qty) in the table


res = frappe.db.sql(f"""SELECT 

 sii.warehouse,sii.item_code,sum(sii.qty) as qty,sli.actual_qty 
from `tabSales Invoice Item` sii


inner join `tabSales Invoice` si on si.name = sii.parent

     
left join  (
          select   sum(actual_qty) as actual_qty,
                       item_code
             from `tabStock Ledger Entry`
             
             
             ) as sli
on sli.item_code = sii.item_code

where

si.status != 'Cancelled'
AND si.status != 'Draft'

group by sii.item_code

“”", as_dict=True)

result = res