SQL Server - group by clause - Asked By shekhar kumar on 15-Jan-10 02:37 AM

I am writing a query to select all the records from a table as follows

SELECT
            v.Voucher_NO as VoucherNo,
            v.Voucher_Id as VID,
            date_format(Voucher_Date,'%d/%c/%Y') as vdate,
            v.Voucher_Type,
            v.Voucher_PayeeName as name,
            v.Voucher_ReceivedBy as receiver,
            e1.Emp_FirstName as a_FName,
            e1.Emp_MiddleName as a_MName,
            e1.Emp_LastName as a_LName,
            e2.Emp_FirstName as c_FName,
            e2.Emp_MiddleName as c_MName,
            e2.Emp_LastName as c_LName,
            v.Voucher_Amount as totoal
            FROM vouchermaster v
            inner join employee e1 on e1.Emp_Id=v.Voucher_CertifiedBy
            inner join employee e2 on e2.Emp_Id=v.Voucher_ApprovedBy
            where  Voucher_Date between 'date1' and 'date2' and Voucher_Status='0' group by v.Voucher_No order by Voucher_Type, Voucher_Date desc

Now there are different type of vouchers like ARL, DV. CRV,PC ect.
Is there any way that I can also get the group sum depending upon the voucher type including all the records.



Use cube and rollup - Kalit Sikka replied to shekhar kumar on 15-Jan-10 02:47 AM

Here you need to use cube and rollup to get summarized resultant.

Please refer: http://databases.about.com/od/sql/l/aacuberollup.htm

thanks for the solution - shekhar kumar replied to Kalit Sikka on 15-Jan-10 03:37 AM

Thanks a lot.
It worked

re - Santhosh N replied to shekhar kumar on 15-Jan-10 03:41 AM

You could use aggregate functions in combination with group by clause..

Something like this..

SELECT region, SUM(Salary)
FROM Employee
WHERE ID BETWEEN AND 10
GROUP BY Region

thanks for the solution - shekhar kumar replied to Santhosh N on 15-Jan-10 04:52 AM
Thanks for your response but goup by caluse will not work in my condition.
I might be wrong.

Here I got an http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/. 

Thanks again.
Re - Manish Mishra replied to shekhar kumar on 19-Jan-10 10:46 AM

Hi Shekhar,

You need to give all the columns in group by unless the particular column is aggregated. your example


SELECT v.Voucher_NO as VoucherNo, v.Voucher_Id as VID, date_format(Voucher_Date,'%d/%c/%Y') as vdate,

v.Voucher_Type, v.Voucher_PayeeName as name, v.Voucher_ReceivedBy as receiver, e1.Emp_FirstName as a_FName,

e1.Emp_MiddleName as a_MName, e1.Emp_LastName as a_LName, e2.Emp_FirstName as c_FName, e2.Emp_MiddleName as c_MName,

e2.Emp_LastName as c_LName, v.Voucher_Amount as totoal FROM vouchermaster v

inner join employee e1 on e1.Emp_Id=v.Voucher_CertifiedBy

inner join employee e2 on e2.Emp_Id=v.Voucher_ApprovedBy

where Voucher_Date between 'date1' and 'date2' and Voucher_Status='0'

group by v.Voucher_NO , v.Voucher_Id , date_format(Voucher_Date,'%d/%c/%Y') ,

v.Voucher_Type, v.Voucher_PayeeName , v.Voucher_ReceivedBy , e1.Emp_FirstName ,

e1.Emp_MiddleName , e1.Emp_LastName , e2.Emp_FirstName , e2.Emp_MiddleName ,

e2.Emp_LastName , v.Voucher_Amount

order by Voucher_Type, Voucher_Date desc

 

Hope it helps

Manish

thanks for reply - shekhar kumar replied to Manish Mishra on 19-Jan-10 11:51 PM
I was looking for group sum. There r different type of voucher r there in the database like JV, DV, ARL etc.
I wanted that when ever it change it's sum should be come in the next row.

It can be done by cube and rollout. But am not able to do it so far.

Voucher NO

Voucher Date

Voucher Type

Payee

Certified By

Approved By

Received By

Amount

ARL-IND-USD-10-00001

19/1/2010 

ARL

SHEKHAR KUMAR 

Philippe SCHOLTES

Vinay VIJ

 

12,000.00 

 

 

 

 

 

 

Arl sum

12,000.00

DV-IND-USD-10-00001

19/1/2010 

DV 

Rajiv kumar 

Philippe SCHOLTES

Vinay VIJ

 

1,600.00 

DV-IND-USD-10-00001

19/1/2010 

DV 

Rajiv kumar 

Philippe SCHOLTES

Vinay VIJ

 

1,600.00 

 

 

 

 

 

 

DV sum

32,000.00

Total