fabzster Posted March 9, 2011 Share Posted March 9, 2011 Hi My MYSQL Version is 5.5.8 I have a query to give me results for a report that will give me all the stores that sold policies from a group during a period. It works however I cannot get it to give me the totals per store. store name store details (managers etc) then below that I print in PHP the actual policies in a list (attached pdf) now I would like the totals for the specific store to be displayed in the header of the store(as long as I get the result from the Mysql query I can place it there with php) SELECT tblpolicies.PolicyNumber , tblpolicies.StoreId , tblpolicies.ConsultantFullName , tblpolicies.DateReceived , tblpolicies.ClientFullName , tblpolicies.Comment , tblpolicies.Query , tblpolicies.PolicyStatus , tblpolicies.DateModified , Groups.GroupName , Groups.StoreName , Groups.StoreTarget , Groups.StoreManager , Groups.PortfolioName , Groups.StoreStatus , Groups.RepName , Groups.ProvinceName FROM tblpolicies LEFT OUTER JOIN ( SELECT StoreId, StoreName, StoreManager, GroupName, StoreTarget, PortfolioName , StoreStatus, RepName, ProvinceName FROM tblstores GROUP BY StoreId ) AS Groups ON tblpolicies.StoreId = Groups.StoreId WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31' AND StoreStatus='ACTIVE' ORDER BY GroupName I also attached some data in pdf(didnt allow me to upload excel files)from the tblstores and tblpolicies tables so that you may see the structure of the data. Hope this helps [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted March 10, 2011 Share Posted March 10, 2011 TLDR... can you be much more specific? Quote Link to comment Share on other sites More sharing options...
fabzster Posted March 10, 2011 Author Share Posted March 10, 2011 I need the below query to also give me a total number of policies per store SELECT tblpolicies.PolicyNumber , tblpolicies.StoreId , tblpolicies.ConsultantFullName , tblpolicies.DateReceived , tblpolicies.ClientFullName , tblpolicies.Comment , tblpolicies.Query , tblpolicies.PolicyStatus , tblpolicies.DateModified , Groups.GroupName , Groups.StoreName , Groups.StoreTarget , Groups.StoreManager , Groups.PortfolioName , Groups.StoreStatus , Groups.RepName , Groups.ProvinceName FROM tblpolicies LEFT OUTER JOIN ( SELECT StoreId, StoreName, StoreManager, GroupName, StoreTarget, PortfolioName , StoreStatus, RepName, ProvinceName FROM tblstores GROUP BY StoreId ) AS Groups ON tblpolicies.StoreId = Groups.StoreId WHERE DateReceived BETWEEN '2011-01-01' AND '2011-01-31' AND StoreStatus='ACTIVE' ORDER BY GroupName Quote Link to comment Share on other sites More sharing options...
fenway Posted March 10, 2011 Share Posted March 10, 2011 Without a subquery, that's not possible -- because you'll only be able to use that value from the policies table. Quote Link to comment Share on other sites More sharing options...
fabzster Posted March 11, 2011 Author Share Posted March 11, 2011 I managed to get a solution: SELECT tblpolicies.PolicyNumber , tblpolicies.StoreId , tblpolicies.ConsultantFullName , tblpolicies.DateReceived , tblpolicies.ClientFullName , tblpolicies.Comment , tblpolicies.Query , tblpolicies.PolicyStatus , tblpolicies.DateModified , Groups.GroupName , Groups.StoreName , Groups.StoreTarget , Groups.StoreManager , Groups.PortfolioName , Groups.StoreStatus , Groups.RepName , Policies.total_policies FROM tblstores AS Groups INNER JOIN tblpolicies ON tblpolicies.StoreId = Groups.StoreId AND tblpolicies.PolicyStatus='ACTIVE' AND tblpolicies.DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}' AND '{$_SESSION['EndDateRepReport']}' INNER JOIN ( SELECT StoreId , COUNT(*) AS total_policies FROM tblpolicies WHERE DateReceived BETWEEN '{$_SESSION['StartDateRepReport']}' AND '{$_SESSION['EndDateRepReport']}' AND tblpolicies.PolicyStatus = 'ACTIVE' GROUP BY StoreId ) AS Policies ON Policies.StoreId = Groups.StoreId WHERE Groups.RepName='{$_SESSION['RepNameReport']}' AND Groups.StoreStatus='ACTIVE' ORDER BY Groups.StoreId , tblpolicies.DateReceived Quote Link to comment Share on other sites More sharing options...
fenway Posted March 11, 2011 Share Posted March 11, 2011 Oh, the count in the subquery -- I misunderstood. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.