Mysql Join Query with Totals

Fabzster · #1 (**permalink**) 03-10-11, 07:14

Hi

I use the query below for a report of stores that have sold policies durning a period. I now need to edit that same query to additionally give me the total policies per store.

Code:

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
  FROM tblpolicies
LEFT OUTER
  JOIN ( SELECT StoreId,
                StoreName, StoreManager, GroupName, StoreTarget, PortfolioName, StoreStatus 
           FROM tblstores
         GROUP
             BY StoreId ) AS Groups
    ON tblpolicies.StoreId = Groups.StoreId
WHERE DateReceived BETWEEN '{$_SESSION['StartDateReport']}' AND '{$_SESSION['EndDateReport']}'
AND GroupName='{$_SESSION['GroupNameReport']}' AND StoreStatus='ACTIVE' ORDER BY StoreId, DateReceived

r937 · #2 (**permalink**) 03-10-11, 09:04

Code:

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
     , Policies.total_policies
  FROM tblstores AS Groups
INNER
  JOIN tblpolicies
    ON tblpolicies.StoreId = Groups.StoreId
   AND tblpolicies.DateReceived BETWEEN '{$_SESSION['StartDateReport']}' 
                                    AND '{$_SESSION['EndDateReport']}'
INNER
  JOIN ( SELECT StoreId
              , COUNT(*) AS total_policies
           FROM tblpolicies
          WHERE DateReceived BETWEEN '{$_SESSION['StartDateReport']}' 
                                 AND '{$_SESSION['EndDateReport']}'
         GROUP
             BY StoreId ) AS Policies
    ON Policies.StoreId = Groups.StoreId
 WHERE Groups.GroupName='{$_SESSION['GroupNameReport']}' 
   AND Groups.StoreStatus='ACTIVE' 
ORDER 
    BY Groups.StoreId
     , tblpolicies.DateReceived

Internet Services

Web Development

Digital Marketing

Consumer Tech