Select count (*) from many tables
Dear friends. I have a requirement to get score (*) from one table with different criteria. I am using hibernate with spring. I am using below query for this.
"select " +
"(select count(*) from enquiry where business_id="+busid+") AS allEnqCount,"+
"(select count(*) from enquiry where status='"+Constants.ENQ_FALLOWUP+"' and us.business_id="+busid+") AS followupCount," +
"(select count(*) from enquiry where status='"+Constants.ENQ_SITE_VISIT+"'and us.business_id="+busid+") AS siteVisitCount ," +
"(select count(*) from enquiry where status='"+Constants.ENQ_CUST_VISIT+"'and us.business_id="+busid+") AS customerVisitCount," +
"(select count(*) from enquiry where status='"+Constants.ENQ_OFFICE_VISIT+"'and us.business_id="+busid+") AS officevisitCount,"+
"(select count(*) from enquiry where status='"+Constants.ENQ_PENDING+"'and us.business_id="+busid+") AS pending";
But it takes some considerable time. Can you tell me that I can do this in minimal time and in one select clause.
Thanks in advance.
+3
source to share
2 answers
use CASE
SELECT SUM(CASE WHEN business_id = 'busid' THEN 1 END) allEnqCount,
SUM(CASE WHEN status = 'ENQ_FALLOWUP' AND us.business_id = 'busid' THEN 1 END) followupCount,
SUM(CASE WHEN status = 'ENQ_SITE_VISIT' AND us.business_id = 'busid' THEN 1 END) siteVisitCount,
SUM(CASE WHEN status = 'ENQ_CUST_VISIT' AND us.business_id = 'busid' THEN 1 END) customerVisitCount,
SUM(CASE WHEN status = 'ENQ_OFFICE_VISIT' AND us.business_id = 'busid' THEN 1 END) officevisitCount,
SUM(CASE WHEN status = 'ENQ_PENDING' AND us.business_id = 'busid' THEN 1 END) pending
FROM enquiry
just change the values to suit your needs.
+2
source to share