Click here to Skip to main content
15,566,615 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Below is my sql query, I have put some MAX(table_name) because when group by is used I can't just put the table name. If I put MAX, it will take time to execute when there is varchar values, please give me good aggregate function for varchar instead of MAX.

Also, those MAX(table_name) all are same values, no different, I just need to print it.

SQL
select distinct t1.PartyName as Customer_Name, SUM(t2.Amount) as Bill_Amount, MAX(t2.VoucherNumber) as Invoice_Number, SUM(i.Weight * t2.Aqty) as Weight, CEILING(SUM(i.CBM * (t2.Aqty/c.Nos))) as CBM, MAX(p.Channel) as Type, MAX(p.RouteNo) as RouteNo, MAX(t1.Adress3) as City
from Item i, Party p, Tran1 t1, VTran2 t2, Cases c
Where t1.VoucherNumber=t2.VoucherNumber and t2.ItemName=i.Itemname and p.PartyName=t1.PartyName and t2.ItemName=c.ItemName and p.RouteNo='" + routeNo1comboBox.Text + "' and t1.LoadingStatus IS NULL and t1.Date BETWEEN '" + startDate + "' and '" + endDate + "' GROUP BY t1.PartyName", cs);
Posted

1 solution

If MAX(table_name) all are same values, no different then add those column names in group by, you will not need to add aggregate function to the column name.
 
Share this answer
 
Comments
abdulsafran 3-Dec-14 7:51am    
Thanks a lot yusuf_ahmed

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900