Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2009
    Posts
    28

    Unanswered: Sum Of Count Help

    HI Guys,
    I am trying to pull the Date and Total Count of ID's from a table. If I write a simple Access (2003) query I can pull the Date and Count from the table, but the data is too long to be copied to Excel (The reason I want to copy it to Excel is to find the total sum of Count). How can I do the Sum of Count and display the date at the same time.

    For ex:
    AS_OF DATE COUNT
    05/01/09 5
    05/01/09 7
    05/01/09 1
    05/01/09 3


    I want to display that as:

    AS_OF DATE COUNT
    05/01/09 16



    Also another question: When I am trying to pull the records(Date and Count) using Group by I am still getting the ouput as
    AS_OF DATE COUNT
    05/01/09 5
    05/01/09 7
    05/01/09 1
    05/01/09 3


    Is there a problem with that?


    Thanks for your help in advance

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Check your grouping, you should have a field to Group by date and a field to count.

  3. #3
    Join Date
    Feb 2009
    Posts
    28
    Hi Allan,
    Thanks for the quick response. Here is my query I tried, this only displays the total Sum, but I'd prefer both Date and Total. Thanks in Advance

    SELECT Sum(B.C) AS C
    FROM [SELECT PUBLIC_MRAD_CACHE.AS_OF, Count(PUBLIC_MRAD_CACHE.SECURITY_ID) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE (((PUBLIC_MRAD_CACHE.AS_OF)=#5/27/2009#) AND ((PUBLIC_MRAD_CACHE.PARENT_COMP_NAME) Is Not Null))
    GROUP BY PUBLIC_MRAD_CACHE.AS_OF, PUBLIC_MRAD_CACHE.PARENT_COMP_NAME]. AS B;

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Have you tried just including it in your SELECT clause?
    Code:
    SELECT PUBLIC_MRAD_CACHE.AS_OF, Sum(B.C) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE (((PUBLIC_MRAD_CACHE.AS_OF)=#5/27/2009#) AND ((PUBLIC_MRAD_CACHE.PARENT_COMP_NAME) Is Not Null))
    GROUP BY PUBLIC_MRAD_CACHE.AS_OF, PUBLIC_MRAD_CACHE.PARENT_COMP_NAME;
    Me.Geek = True

  5. #5
    Join Date
    Feb 2009
    Posts
    28
    Yup I tried that before, it gave me the warning...
    "You tried to execute a query that does not include the specified expression 'AS_OF' as part of an aggregated function

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    What does the table structure of PUBLIC_MRAD_CACHE look like? Field names, types, etc...
    Me.Geek = True

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    kvkanuri, I am a little confused on exactly what you want. Way back in the first post you asked:
    Code:
    Also another question: When I am trying to pull the 
    records(Date and Count) using Group by 
    I am still getting the ouput as
    AS_OF DATE COUNT
    05/01/09 5
    05/01/09 7
    05/01/09 1
    05/01/09 3
    
    Is there a problem with that?
    with this (or very similar) SQL:
    Code:
    SELECT AS_OF
         , Sum(B.C) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE AS_OF = 5/27/2009#
      AND PARENT_COMP_NAME Is Not Null
    GROUP BY AS_OF
           , PARENT_COMP_NAME;
    The reason for the multiple dates of the same value is because PARENT_COMP_NAME is in the Group By. You can see what is going on if you include PARENT_COMP_NAME in the Select:

    Code:
    SELECT PARENT_COMP_NAME
          , AS_OF
         , Sum(B.C) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE AS_OF = #05/27/2009#
      AND PARENT_COMP_NAME Is Not Null
    GROUP BY AS_OF
           , PARENT_COMP_NAME;
    With results similar to:
    AS_OF DATE COUNT
    CompanyA 05/27/2009 5
    CompanyB 05/27/2009 7
    CompanyC 05/27/2009 1
    CompanyD 05/27/2009 3

    Which leads me back to exactly what you want as a result. If it is just Unique dates and their total count then just remove PARENT_COMP_NAME from the Group By.

    You also mentioned something about taking all the output from the Unique Date and Sum an putting it into EXCEL to get a total Count (for all dates I believe).

    You can do that with a second query, if you like. It would either be a similar query with less filtering or you could run a query with the previous query as a nested table.

    Once I know exactly what you are looking for, I might be able to be more specific.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing to keep in mind regarding dates (and times) from MSAccess to Excel. If you're exporting the data from MSAcess to an excel format (or even updating certain Excel cells), Excel doesn't default to the date AND time format (it only defaults to the date). After you've exported/updated the excel file, highlight the date/time fields and change the format in excel to show the time along with the date (and then expand the Excel field to show the entire date and time.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2009
    Posts
    28
    I tried using this query:
    SELECT DATEASOF, Sum(C) AS COUNTSEC
    FROM [SELECT PUBLIC_MRAD_CACHE.AS_OF AS DATEASOF, Count(PUBLIC_MRAD_CACHE.SECURITY_ID) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE (((PUBLIC_MRAD_CACHE.AS_OF)=#5/27/2009#) AND ((PUBLIC_MRAD_CACHE.PARENT_COMP_NAME) Is Not Null))
    GROUP BY PUBLIC_MRAD_CACHE.AS_OF]. AS B;

    It gives me the same warning:
    "You tried to execute a query that does not include the specified expression 'AS_OF' as part of an aggregated function"

    I want my final output to be displayed as

    AS_OF DATE COUNT
    05/27/09 16

    I removed the group by Parent_comp_Name but still I am having problems

    The AS_OF is a data type: Date/Time and the Security ID I want the Count od is a Datatype: Text. Hope I covered everything. But I greatly appreciate all you guys for helping me. Thanks once again

  10. #10
    Join Date
    Feb 2009
    Posts
    28
    Quote Originally Posted by pkstormy
    One thing to keep in mind regarding dates (and times) from MSAccess to Excel. If you're exporting the data from MSAcess to an excel format (or even updating certain Excel cells), Excel doesn't default to the date AND time format (it only defaults to the date). After you've exported/updated the excel file, highlight the date/time fields and change the format in excel to show the time along with the date (and then expand the Excel field to show the entire date and time.)

    I manually copy data from Access to Excel...So I guess this shouldnt be a worry for me, unless I am not misinterpreting your suggestion. Thanks

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    kvkanuri, Try this instead:
    Code:
    SELECT DATEASOF, Sum(C) AS COUNTSEC
    FROM (SELECT PUBLIC_MRAD_CACHE.AS_OF AS DATEASOF
               , Count(PUBLIC_MRAD_CACHE.SECURITY_ID) AS C
          FROM PUBLIC_MRAD_CACHE
          WHERE PUBLIC_MRAD_CACHE.AS_OF = #5/27/2009# 
            AND PUBLIC_MRAD_CACHE.PARENT_COMP_NAME Is Not Null
          GROUP BY PUBLIC_MRAD_CACHE.AS_OF
         ) AS B
    GROUP BY DATEASOF
    ;
    You had one Group By for the Count but were missing the second Group By for the Sum.

    However, it doesn't appear you really need the nested table. This should give the same result:
    Code:
    SELECT PUBLIC_MRAD_CACHE.AS_OF AS DATEASOF
         , Count(PUBLIC_MRAD_CACHE.SECURITY_ID) AS C
    FROM PUBLIC_MRAD_CACHE
    WHERE PUBLIC_MRAD_CACHE.AS_OF = #5/27/2009# 
      AND PUBLIC_MRAD_CACHE.PARENT_COMP_NAME Is Not Null
    GROUP BY PUBLIC_MRAD_CACHE.AS_OF
    ;
    This will give you the total number of rows whose AS_OF date is 05/27/2009 and PARENT_COMP_NAME is NOT NULL.

  12. #12
    Join Date
    Feb 2009
    Posts
    28
    That Works like a Charm bro...Thanks for the help. I donno what I did wrong before...Looks like my Group by on the outside was missing...Thanks once again...

Posting Permissions

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