如何获取具有连接的查询的计数

帕尼库马尔

我写了一个带有连接的选择查询，它提供了 100 条记录，但我需要获取从数据库中获取的相同查询的记录数

我试过下面提到的东西，但它给出了错误的计数。

SELECT COUNT(vm.vendor_id)
         FROM vendor_master vm LEFT JOIN purchase_order po ON po.vendor_id=vm.vendor_id AND  vm.`is_active`=TRUE AND vm.`organization_id`=1



SELECT vm.vendor_id vendor_id,COUNT(po.Purchase_id) TOTAL_PURCHASES, vm.Vendor_Name,
MAX((SELECT vc.Contact_Name FROM vendor_contacts vc WHERE po.vendor_cont_id=vc.Vend_Cont_id)) contact_name,
SUM((SELECT SUM(pd.pur_amt) FROM purchase_details pd WHERE po.Purchase_id=pd.Purchase_id)) Total_amount,
(SELECT SUM(pm.Amount) FROM payment_master pm WHERE pm.Vendor_id = vm.vendor_id) Paid,
(SUM((SELECT SUM(pd.pur_amt) FROM purchase_details pd WHERE po.Purchase_id=pd.Purchase_id)) -
(SELECT SUM(pm.Amount) FROM payment_master pm WHERE pm.Vendor_id = vm.vendor_id)) Balance
FROM vendor_master vm LEFT JOIN purchase_order po ON po.vendor_id=vm.vendor_id AND  vm.`is_active`=TRUE AND vm.`organization_id`=1
GROUP BY vm.vendor_id

以上是我需要从中获取计数的实际查询

阿卡什·波帕特

SELECT COUNT(*) FROM
(
SELECT vm.vendor_id vendor_id,COUNT(po.Purchase_id) TOTAL_PURCHASES, vm.Vendor_Name,
MAX((SELECT vc.Contact_Name FROM vendor_contacts vc WHERE po.vendor_cont_id=vc.Vend_Cont_id)) contact_name,
SUM((SELECT SUM(pd.pur_amt) FROM purchase_details pd WHERE po.Purchase_id=pd.Purchase_id)) Total_amount,
(SELECT SUM(pm.Amount) FROM payment_master pm WHERE pm.Vendor_id = vm.vendor_id) Paid,
(SUM((SELECT SUM(pd.pur_amt) FROM purchase_details pd WHERE po.Purchase_id=pd.Purchase_id)) -
(SELECT SUM(pm.Amount) FROM payment_master pm WHERE pm.Vendor_id = vm.vendor_id)) Balance
FROM vendor_master vm LEFT JOIN purchase_order po ON po.vendor_id=vm.vendor_id AND  vm.`is_active`=TRUE AND vm.`organization_id`=1
GROUP BY vm.vendor_id
) a;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。