I have a join query which I use for a report
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 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