SELECT Table1.agents AS Vendor
, Count(Table1.carS) AS Car_Sold
, Sum(Table1.carP) AS Car_Price
, Count(Table1.busS) AS MortBus_Sold
, Sum(Table1.busP) AS busPRice
,  
(
SELECT SUM(Table1.carS) 
FROM Table1
WHERE (condition='Yes')
) 
AS Car_bought_price, 
(
SELECT COUNT(Table1.carB) 
FROM Table1 
WHERE  (condition='Yes')
) 
AS Good_condition
FROM Table1
WHERE (carS='Sold' Or busS=''Sold' ')
GROUP BY Table1.agents ;

Table1
 Columns:
 agents = John, Bill, Frank...
 carS = Sold, Unsold
 carP = Car selling price
 busS = Sold, unsold
 busP = Bus selling price
 condition = Yes or No
 carB = car buying price

SELECT Table1.agents AS Vendor
, Count(Table1.carS) AS Car_Sold
, Sum(Table1.carP) AS Car_Price
, Count(Table1.busS) AS MortBus_Sold
, Sum(Table1.busP) AS busPRice
,  SUM(CASE WHEN condition='Yes' THEN Table1.carS ELSE 0 END) AS  Car_bought_price
,  COUNT(CASE WHEN condition='Yes' THEN Table1.carB ELSE NULL END) AS  Good_condition
FROM Table1
WHERE (carS='Sold' Or busS=''Sold' ')
GROUP BY Table1.agents ;