Mysql Join Query with Totals

[fabzster]

Hi

I have a join query which I use for a report
ok

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

[smantscheff]

You can include aggregate functions for the stores in you Group subquery.

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
     , Groups.NumberOfPolicies

  FROM tblpolicies
LEFT OUTER
  JOIN ( SELECT StoreId,
                StoreName, StoreManager, GroupName, StoreTarget, PortfolioName
                , StoreStatus, RepName, ProvinceName 
                , (SELECT count(*) FROM tblpolicies where StoreId = tblstores.StoreId) as NumberOfPolicies
           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

For further help submit a complete test case with CREATE TABLE statements, INSERT statements for test data and the relevant queries.

[fabzster]

Thank You

I managed to find a solution though

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

Hope this helps someone else