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

Recommended Answers

All 2 Replies

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.