Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2007
    Posts
    45

    Unanswered: Union queries with Count function

    I am trying to calculate how many pieces of equipment are in for repair or preventative maintenance (two different tables) within the equipment's category and subcategory. I have two queries for each table that work out just fine, but I need to combine them and have them calculate the total number for each subcategory. I have created a union query that returns all the records, but I need to have it add the two tables together so that each subcategory only appears once with the sum total from each table. I am clueless as to how to do this and can't find any help in the forum history or elsewhere on the internet. Database is attached and my SQL statement is below:

    Thanks in advance for your help!

    SELECT tblCategory.CategoryName, tblSubCategory.SubCatName, Count(tblGSR.EquipID) AS CountOfEquipID, tblGSR.Closed
    FROM (tblCategory INNER JOIN tblSubCategory ON tblCategory.CategoryID=tblSubCategory.CategoryID) INNER JOIN (tblEquip INNER JOIN tblGSR ON tblEquip.EquipID=tblGSR.EquipID) ON tblSubCategory.SubCatID=tblEquip.SubCategoryID
    GROUP BY tblCategory.CategoryName, tblSubCategory.SubCatName, tblGSR.Closed
    HAVING (((tblGSR.Closed)=0))
    UNION ALL
    SELECT tblCategory.CategoryName, tblSubCategory.SubCatName, Count(tblPM.EquipID) AS CountOfEquipID, tblPM.Closed
    FROM (tblCategory INNER JOIN tblSubCategory ON tblCategory.CategoryID = tblSubCategory.CategoryID) INNER JOIN (tblEquip INNER JOIN tblPM ON tblEquip.EquipID = tblPM.EquipID) ON tblSubCategory.SubCatID = tblEquip.SubCategoryID
    GROUP BY tblCategory.CategoryName, tblSubCategory.SubCatName, tblPM.Closed
    HAVING (((tblPM.Closed)=0));
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CategoryName
         , SubCatName
         , SUM(GSR_subtotal) as GSR_total
         , SUM(PM_subtotal) as PM_total
      FROM (
           SELECT tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , Count(tblGSR.EquipID) AS GSR_subtotal
                , 0                     AS PM_subtotal
             FROM ((
                  tblCategory 
           INNER 
             JOIN tblSubCategory 
               ON tblSubCategory.CategoryID = tblCategory.CategoryID
                  )
           INNER 
             JOIN tblEquip 
               ON tblEquip.SubCategoryID = tblSubCategory.SubCatID
                  ) 
           INNER 
             JOIN tblGSR 
               ON tblGSR.EquipID = tblEquip.EquipID
            WHERE tblGSR.Closed = 0 
           GROUP 
               BY tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , tblGSR.Closed
           UNION ALL
           SELECT tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , 0                   
                , Count(tblPM.EquipID)
             FROM ((
                  tblCategory 
           INNER 
             JOIN tblSubCategory 
               ON tblSubCategory.CategoryID = tblCategory.CategoryID
                  )
           INNER 
             JOIN tblEquip 
               ON tblEquip.SubCategoryID = tblSubCategory.SubCatID
                  ) 
           INNER 
             JOIN tblPM 
               ON tblPM.EquipID = tblEquip.EquipID
            WHERE tblPM.Closed = 0
           GROUP 
               BY tblCategory.CategoryName
                , tblSubCategory.SubCatName
           ) as D
    GROUP
        BY CategoryName
         , SubCatName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It can probably be done in one SQL statement but I don't know the syntax (someone else might), but I would do the UNION first (call it query1) and then in a separate query do the sum (call it query2).

    When I need to create several queries to generate the data I need I will use a name convention that makes it clear that this is one query in a group of queries. Ex.

    qryGatherReportData001GetDetailData
    qryGatherReportData005AddTotals
    qryGatherReportData010FinalData

    Notice I leave gaps in case I need to add a query in between. It keeps all the queries together and ordered. Just an FYI.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I figured r937 could do it in one statement.

  5. #5
    Join Date
    Apr 2007
    Posts
    45
    Thank r937. I am able to use that for what I need as I can create another query that adds the PM_subtotal and GSR_subtotal columns together to give me the total for each subcategory.

    Is there a way to write the union query so that it does not come back with 2 columns for each subcategory, but instead, adds the two [CountofEquipID] into one column - otherwise, comes back with an Overall total for each category? I don't know if I am phrasing my question in a clear way.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure, that's easy --
    Code:
    SELECT CategoryName
         , SubCatName
         , SUM(Equip_subtotal) as Equip_total
      FROM (
           SELECT tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , Count(tblGSR.EquipID) AS Equip_subtotal
             FROM ((
                  tblCategory 
           INNER 
             JOIN tblSubCategory 
               ON tblSubCategory.CategoryID = tblCategory.CategoryID
                  )
           INNER 
             JOIN tblEquip 
               ON tblEquip.SubCategoryID = tblSubCategory.SubCatID
                  ) 
           INNER 
             JOIN tblGSR 
               ON tblGSR.EquipID = tblEquip.EquipID
            WHERE tblGSR.Closed = 0 
           GROUP 
               BY tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , tblGSR.Closed
           UNION ALL
           SELECT tblCategory.CategoryName
                , tblSubCategory.SubCatName
                , Count(tblPM.EquipID)
             FROM ((
                  tblCategory 
           INNER 
             JOIN tblSubCategory 
               ON tblSubCategory.CategoryID = tblCategory.CategoryID
                  )
           INNER 
             JOIN tblEquip 
               ON tblEquip.SubCategoryID = tblSubCategory.SubCatID
                  ) 
           INNER 
             JOIN tblPM 
               ON tblPM.EquipID = tblEquip.EquipID
            WHERE tblPM.Closed = 0
           GROUP 
               BY tblCategory.CategoryName
                , tblSubCategory.SubCatName
           ) as D
    GROUP
        BY CategoryName
         , SubCatName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    45
    Thank you! I will be able to use both of these for different reports I need to build. How can someone who is not a computer person learn SQL and VBA - I am having to post here more than I would like to.

  8. #8
    Join Date
    Apr 2007
    Posts
    45

    Question Ooops, this query did not solve all my problems

    I thought the query above was all that I needed but when I went to build the report, I realized I had not thought through all the needed criteria.

    I am trying to build a report with 3 subreports and it is the 3rd sub that is giving me the problem:

    1. 1st subreport lists in detail all equipment that is out for repair (GSR). The criteria to determine what is out is a "0" in the "Closed" column of GSR table.

    2. 2nd report lists all the equipment that is out for preventative maintenance (PM). Because we preload upcoming PM's into the system, and piece of equipment could have lots of open PM tickets in it. Therefore, the criteria to determine what is actually out of service is a "0" and "Is not Null" in "Start date" (column is not filled out until they actually pull it out to work on it.)

    3. 3rd subreport - I want to list the total pieces of equipment that are out by each category, subcategory. The above query that r937 so generously provided to me does this. However, it will pull all equipment that has open PM tickets and therefore, the report will not be correct. For the PM's, I need it to pull only equipment that have "0" and "Is not Null" in "Start Date" and then add that number together to give me the total out per subcategory. However, from what I understand about union queries, all the criteria from the two tables needs to be the same. Is there a way to accomplish what I am trying to accomplish?

  9. #9
    Join Date
    Apr 2007
    Posts
    45
    Oh one more piece of information: the GSR table does not have a "Start Date", but instead has a "Reported Date" - which is the reason why I can't make all the criteria even for both tables.

  10. #10
    Join Date
    Apr 2007
    Posts
    45
    Okay, I have renamed the GSR table ReportedDate to StartDate. However, when I try to amend the above query to include WHERE StateDate Is Not Null, I keep getting a syntax error, so I am not doing something right. Anybody have any ideas of what I am doing wrong?

  11. #11
    Join Date
    Apr 2007
    Posts
    45
    Nevermind, I figured it out. I had a parenthesis where it shouldn't have been.

Posting Permissions

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