Click here to Skip to main content
15,941,444 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
i want report , in that report simultaneously i can pass 1 query

here the query contains details of visitors but i want total count of data
means vis_Id,Vis_name,CompanyName,Visited

Here visited is count
Posted

Hi
Try like this
SQL
SELECT  vis_Id,Vis_name,CompanyName, COUNT(Vis_id) 'Visited' FROM VisiterDtls 
GROUP BY  vis_Id,Vis_name,CompanyName

Regards
GVPrabu
 
Share this answer
 
Comments
AnnuBhai 23-Feb-13 7:10am    
my query like this

SELECT
`vis_app_visitdetails`.`PVIS_ID`
, `vis_app_visitors`.`VisName`
, `vis_app_company`.`ComName`
,COUNT(`vis_app_visitdetails`.`PVIS_ID`) 'Visited'
FROM
`smarthawkattendance`.`vis_app_visitors`
LEFT JOIN `smarthawkattendance`.`vis_app_company`
ON (`vis_app_visitors`.`companyID` = `vis_app_company`.`companyID`)
LEFT JOIN `smarthawkattendance`.`vis_app_visitdetails`
ON (`vis_app_visitors`.`PVIS_ID` = `vis_app_visitdetails`.`PVIS_ID`)
where (vis_app_visitdetails.dtVis_DateTime BETWEEN '2013-02-05 00:00:01'
AND '2013-02-05 23:59:59' ) AND (vis_app_visitors.PVIS_ID='V0202130015' and vis_app_visitors.PVIS_ID='V0502130021');
AnnuBhai 23-Feb-13 7:11am    
It will not give right answer
gvprabu 23-Feb-13 7:31am    
Hi ,

I posted new Solution for your problem.... just check that.

One suggestion : Give small and proper Table, Column alias... :-) :-)
Hi AnnuBhai,

Try like this....
SQL
SELECT vis_app_visitdetails.PVIS_ID , 
vis_app_visitors.VisName , 
vis_app_company.ComName ,
VD.VisitedCount Visited 
FROM smarthawkattendance.vis_app_visitors 
LEFT JOIN smarthawkattendance.vis_app_company ON (vis_app_visitors.companyID = vis_app_company.companyID) 
LEFT JOIN (SELECT PVIS_ID, COUNT(PVIS_ID)VisitedCount FROM smarthawkattendance.vis_app_visitdetails) VD  ON (VD.PVIS_ID=vis_app_visitors.PVIS_ID = ) 
WHERE (vis_app_visitdetails.dtVis_DateTime BETWEEN '2013-02-05 00:00:01' AND '2013-02-05 23:59:59' ) 
AND (vis_app_visitors.PVIS_ID=V0202130015 and vis_app_visitors.PVIS_ID=V0502130021);


Regards,
GVPrabu
 
Share this answer
 
v2
Comments
AnnuBhai 23-Feb-13 7:39am    
SELECT
`vis_app_visitdetails`.`PVIS_ID`
, `vis_app_visitors`.`VisName`
, `vis_app_company`.`ComName`
,COUNT(`vis_app_visitdetails`.`PVIS_ID`) 'Visited'
FROM
`smarthawkattendance`.`vis_app_visitors`
LEFT JOIN `smarthawkattendance`.`vis_app_company`
ON (`vis_app_visitors`.`companyID` = `vis_app_company`.`companyID`)
LEFT JOIN `smarthawkattendance`.`vis_app_visitdetails`
ON (`vis_app_visitors`.`PVIS_ID` = `vis_app_visitdetails`.`PVIS_ID`)
where (vis_app_visitdetails.dtVis_DateTime BETWEEN '2013-02-05 00:00:01'
AND '2013-02-05 23:59:59' ) AND (vis_app_visitors.PVIS_ID='V0202130015' or vis_app_visitors.PVIS_ID='V0502130021')
group by `vis_app_visitdetails`.`PVIS_ID`,`vis_app_visitors`.`VisName` , `vis_app_company`.`ComName`;





Solved


Thanx Broooooooo
For Rply me
gvprabu 23-Feb-13 10:29am    
Hi Due,

Try some other method... If you will give all Selected Columns in Group BY then some time you will different output.
Ok fine....

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