select m.operator,s.scode,a.OCCASIONTYPE ,count(*) from msg_info m,shortcode s,operator op,account a where m.scode_id=s.id and m.operator=op.operator and m.accid=a.id and m.operator in ('SABAFON','mobi') and s.scode in ('4646','4050') and a.occasiontype in ('wedding','anniversary')
group by m.operator,s.scode,a.OCCASIONTYPE;

String queryString = "SELECT m.operator as operator,sc.scode as shortCode,a.occasionType as occasionType,count(*) as total from msg_info m ,account a,operator op,shortcode sc where  m.ACCID=a.ID and m.operator = op.operator and m.scode_id = sc.id and op.id in "+ subquery + " and sc.id in "+ subquery2 + " group by m.operator ,m.scode_id ,a.occasionType ";

List list = session.createSQLQuery(queryString).addScalar("operator", Hibernate.STRING).addScalar("shortCode", Hibernate.STRING).addScalar("occasionType", Hibernate.STRING).addScalar("total", Hibernate.LONG).list();
   
for(Iterator it1 = list.iterator();it1.hasNext();)
{
   Object[] row = (Object[]) it1.next();
   System.out.println("operator ----- >>> > > > > >: " + row[0]);
   System.out.println("short code ----- >>> > > > > >" + row[1]);
   System.out.println("occasion type ----- >>> > > > > >"+ row[2]);
   System.out.println("total  ----- >>> > > > > > " + row[3]);
}