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] Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/ Share on other sites More sharing options...
fenway Posted March 10, 2011 Share Posted March 10, 2011 TLDR... can you be much more specific? Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/#findComment-1185334 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 Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/#findComment-1185454 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. Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/#findComment-1185550 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 Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/#findComment-1186041 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. Link to comment https://forums.phpfreaks.com/topic/230072-mysql-join-query-with-totals/#findComment-1186297 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.