Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Exclamation Unanswered: Mysql Join Query with Totals

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •