Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Creating Subquery to Sum for a Particular Week

    I'm having a bit of trouble trying to figure out a query and I'm hoping one of you geniuses out there can help me out .

    Basically, what I'm trying to do is get the sum of a currency field (call it 'Total Amt') for a particular field (call it 'Field1') for a particular week of the year (let's say Week 01 of 2009). I think this requires a subquery (or inner query or whatever you want to call it), but so far I haven't been able to figure it out. This is what I've got so far (simplified):
    Code:
    SELECT TOP 10 qrySource.Field1, SUM(qrySource.[Total Amt]), 
        (SELECT Sum(qrySource.[Total Amt])
        FROM qrySource
        WHERE (((FORMAT(qrySource.[Paid Date],'yyyy') & '-W' & FORMAT(FORMAT(qrySource.[Paid Date],'ww'),'00')) >= (FORMAT(NOW() - 7*52,'yyyy') & '-W' & FORMAT(FORMAT(NOW() - 7*52,'ww'),'00'))) AND 
            (qrySource.[Field2] IN ('Alpha','Beta')))) 
        AS [Critical Field]
    FROM qrySource
    WHERE ((qrySource.[Field2] IN ('Alpha','Beta')) AND (qrySource.Field1 <> ''))
    GROUP BY (qrySource.Field1)
    ORDER BY SUM(qrySource.[Total Amt]) DESC;
    The trouble is that I can't get it to sum only for the Field1 in question. If I can just get a query to work for a particular week, I think I can generalize it and make my code write it dynamically based on user input. But for now, I just need it to work for one example.

    Also, I know that subqueries are a weakness of mine, so if anyone knows any good tutorials anywhere that can help me with them, I would be all ears. Thanks!
    Last edited by nckdryr; 05-25-09 at 20:04.
    Me.Geek = True

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    nckdryr, what version of Access are you using? It can make a difference what functions are available.

    What is the input, Week number or a Date?

    The basic SQL for what you want is:

    SELECT Field1, SUM(TOTAL_AMT)
    FROM QrySource
    WHERE Claims_Paid_date between start-of-week and end-of-week
    AND any other filering you may need
    GROUP BY Field1

    The difficult part is figuring out the Start and End of the week which is where the available functions come in.

    If the Input is a week number, is there a function to get some date out of it?
    Are there any functions to determine the day of the week?
    (by the way is you week Sun-Sat, Mon-Sun, Mon-Fri, other?)

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Stealth,

    The database is being developed for Access 2003.

    The field is a date, which is why I use the formats, which take care of the sum of the week (they end up looking like 2009-W08, 2009-W09, 2009-W10, etc.), that's not the problem. The problem is getting the subquery to only sum for the respective Field1 group rather than all Field1 groups.

    I guess I did simplify the query poorly, this would be better:
    Code:
    SELECT TOP 10 qrySource.Field1, SUM(qrySource.[Total Amt]), 
        (SELECT Sum(qrySource.[Total Amt])
        FROM qrySource
        WHERE (((FORMAT(qrySource.[Paid Date],'yyyy') & '-W' & FORMAT(FORMAT(qrySource.[Paid Date],'ww'),'00')) = '2009-W01') AND 
            (qrySource.[Field2] IN ('Alpha','Beta')))) 
        AS [Critical Field]
    FROM qrySource
    WHERE ((qrySource.[Field2] IN ('Alpha','Beta')) AND (qrySource.Field1 <> ''))
    GROUP BY (qrySource.Field1)
    ORDER BY SUM(qrySource.[Total Amt]) DESC;
    Thanks for any help you can offer.
    Last edited by nckdryr; 05-25-09 at 20:04.
    Me.Geek = True

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The problem is getting the subquery to only sum for the respective Field1 group rather than all Field1 groups.
    Okay, looking at the subquery:

    It will retrieve all rows where Field2 is 'Alpha' or 'Beta' and the date can be formatted to equal 2009-W01 (or whatever will be entered).

    There is nothing to limit the Field1 values. All the various values for Field1 (of the rows passing the filtering) will be included in the Sum.

    You might try adding:
    Code:
    (SELECT Sum(qrySource.[Total Amt])
           FROM qrySource
           WHERE FORMAT(qrySource.[Paid Date],'yyyy') & '-W' & FORMAT(FORMAT(qrySource.[Paid Date],'ww'),'00') = '2009-W01' 
             AND qrySource.[Field2] IN ('Alpha','Beta')
             AND qrySource.Field1 = xxxx
          )  AS [Critical Field]
    where xxxx is the Field1 value you want the Sum of.

    Or am I still not understanding the problem?

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Well, yes, I think you are understanding my problem now. The problem is that I need the subquery to return the sum value for each respective Field1.

    Perhaps if I illustrate with examples what I'm after, it may proove beneficial. Let's say Field1 has values of either Apples, Bananas, or Cherries. And for the week in question, 67 apples were sold, 89 Bananas and 45 Cherries.

    So to use my query, it would return results like:

    Field1 | Sum | Week Sum
    Apples | 5000 | 201
    Bananas | 3400 | 201
    Cherries | 21000 | 201

    (201 = 67 + 89 + 45)

    Then using your query where I specify the 'xxxx' to be say 'Apples', the query would return:

    Field1 | Sum | Week Sum
    Apples | 5000 | 67
    Bananas | 3400 | 67
    Cherries | 21000 | 67

    I'm trying to make it return:

    Field1 | Sum | Week Sum
    Apples | 5000 | 67
    Bananas | 3400 | 89
    Cherries | 21000 | 45

    I hope that clarifies things now. Thank you for your help so far Stealth.
    Me.Geek = True

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The example, definitely helps. I will make an assumption that the second column SUM is a Sum of all dates in the table (for 'Alpha' and 'Beta').

    Field1 | Sum | Week Sum
    Apples | 5000 | 67
    Bananas | 3400 | 89
    Cherries | 21000 | 45

    I think what you need is more of a join then a subquery. The following SQL will work in my database but I am not sure about access. If not, you should be able to make the necessary adjustments.
    Code:
    SELECT Total_Total.Field1
         , Total_sum
         , Week_SUM
    FROM (SELECT Field1
               , SUM([Total Amt]) as Total_SUM
          FROM qrySoruce
          WHERE Field2 IN ('Alpha','Beta')
            AND Field1 <> ''
          GROUP BY Field1
         ) as Total_Total
       , 
         (SELECT Field1
               , SUM(qrySource.[Total Amt]) as Week_SUM
          FROM qrySource
          WHERE  FORMAT([Paid Date],'yyyy') 
               & '-W' 
               & FORMAT(FORMAT([Paid Date],'ww'),'00') >= 
                 FORMAT(NOW() - 7*52,'yyyy') 
               & '-W' 
               & FORMAT(FORMAT(NOW() - 7*52,'ww'),'00')
            AND Field2 IN ('Alpha','Beta') 
            AND Field1 <> ''
          GROUP BY Field1
         ) AS Week_Total
    WHERE Total_Total.Field1 = Week_Total.Field1
    That is how I would conceptualize it. (You would need to add the TOP and ORDER BY). However, if you can use CASE, this might work better as it only has to go through the table once.
    Code:
    SELECT TOP 10
           Field1
         , SUM(qrySource.[Total Amt]) AS Total_Amt
         , SUM(CASE WHEN FORMAT(qrySource.[Paid Date],'yyyy') 
               & '-W' 
               & FORMAT(FORMAT(qrySource.[Paid Date],'ww'),'00') >= 
                 FORMAT(NOW() - 7*52,'yyyy') 
               & '-W' 
               & FORMAT(FORMAT(NOW() - 7*52,'ww'),'00')  THEN [Total Amt]
                                                         ELSE 0
               END
              ) AS Week_Total
    FROM qrySource
    WHERE Field2 IN ('Alpha','Beta')
      AND Field1 <> ''
    GROUP BY Field1
    ORDER BY Total_Amt DESC;

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Of course, a join! Thanks Stealth! I give it a shot and let you know how it works out.
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Stealth,

    I actually ended up using your CASE idea. Access (I don't think) has that same syntax, but I was able to use an IIF statement nested inside of a SUM to get what I want. Serves me right for trying to code on a weekend, can't think of the simple answer.

    Thanks so much!
    Me.Geek = True

Posting Permissions

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