SubQuery

PRINCIPLES
1.(SELECT...)
2.can use with SELECT,FROM,WHERE,HAVING

EX1
SELECT CustTable.CustID, CustTable.balance, (

SELECT SUM( CustTable.balance )
FROM CustTable

), CustTable.balance / ( 
SELECT SUM( CustTable.balance ) 
FROM CustTable ) *100

FROM `custtable`

EX2
SELECT * FROM CustTable WHERE CustTable.Balance >= ( SELECT avg(CustTable.Balance) FROM CustTable );
EX3 SELECT iCityID, sCityName FROM citymaster c where iCityID in(SELECT iStateID FROM statemaster s)

Over Dues and Over balance Customer

SELECT CustTable.CustName,CustTable.Balance ,Sum(SalesLine.LineAmt),Salestable.DuesDate
FROM CustTable,Salestable,SalesLine
WHERE CustTable.CustID = Salestable.CustID
  And Salestable.SalesID = SalesLine.SalesID
  And Salestable.DuesDate > now()
  and CustTable.Balance > 1000
GROUP BY CustTable.CustName,CustTable.Balance,Salestable.DuesDate
Order By CustTable.Balance  Desc

Max Balance Customer

SELECT CustTable.CustName,Max(CustTable.Balance)
FROM CustTable
Group by CustTable.CustID
Order by Max(CustTable.Balance) Desc
Limit 0,1

Top 10 Item

Select InventTable.ItemID,sum(SalesLine.LineAmt)
FROM SalesLine,SalesTable,InventTable
WHERE SalesTable.SalesID = SalesLine.SalesID
  And InventTable.ItemID= SalesLine.ItemID
Group By InventTable.ItemID
Order By sum(SalesLine.LineAmt) DESC
Limit 0,10

Top 10 Customer

Select CustTable.CustName,sum(SalesLine.LineAmt)
FROM SalesLine,SalesTable,CustTable
WHERE SalesTable.SalesID = SalesLine.SalesID
  And SalesTable.CustID = CustTable.CustID
Group By CustTable.CustName
Order By sum(SalesLine.LineAmt) DESC
Limit 0,10