Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
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 23-Feb-13 0:38am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi
Try like this
SELECT  vis_Id,Vis_name,CompanyName, COUNT(Vis_id) 'Visited' FROM VisiterDtls 
GROUP BY  vis_Id,Vis_name,CompanyName
Regards
GVPrabu
  Permalink  
Comments
C M Senergy at 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');
C M Senergy at 23-Feb-13 7:11am
   
It will not give right answer
gvprabu at 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... :-) :-)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi AnnuBhai,
 
Try like this....
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
  Permalink  
v2
Comments
AnnuBhai at 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 at 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)

  Print Answers RSS
0 OriginalGriff 310
1 Sergey Alexandrovich Kryukov 176
2 PhilLenoir 164
3 Richard MacCutchan 160
4 Sharmanuj 146
0 Sergey Alexandrovich Kryukov 6,081
1 OriginalGriff 5,115
2 CPallini 2,473
3 Richard MacCutchan 1,597
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 23 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100