Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2014
    Posts
    3

    Unanswered: Query to return distinct counts of two attributes from single table

    Hi,
    I have a query based on a single table of items in invoices (approx. 30,000 records; named X) with subqueries performing distinct counts and summation.
    The table looks something like this (one record = one item):

    Document_ID....Item_No...Item_Class...Amount

    ____1__________1__________X_________10
    ____1__________2__________X_________20
    ____1__________3__________Y_________25
    ____2__________1__________Y_________16
    ____2__________2__________Y_________22
    ____2__________3__________Z_________45
    ____2__________4__________X_________40


    The code (see below) is a reworked version of what I found on a blog. It is supposed to give me a list of distinct document_ids with
    • Count of distinct classes per document_id
    • count of items per document_id
    • sum of amount (over items) per document_id


    Code:
    SELECT Main.document_id
               , (
                   SELECT 
                          Count(tmp1.class) 
                   FROM 
                           (
                            SELECT DISTINCT 
                                    document_id
                                  , class 
                            FROM X 
                            ORDER BY 
                                  document_id
                                , class
                           )
                            AS tmp1
    
                   WHERE 
                           Main.document_id = tmp1.document_id
                   GROUP BY 
                           tmp1.document_id 
                   ORDER BY 
                           tmp1.document_id
                  ) 
                   AS Distinct_cnt_class
    
               , Count(Main.item_no) AS cnt_items
               , Sum(Main.amount) AS tot_amount
    
    INTO TableXYZ
    FROM X AS Main
    GROUP BY Main.document_id
    ORDER BY Main.document_id;
    My questions:

    1. The query takes a really long time to execute. How do I speed it up?
    I have seen people suggesting including INNER JOINs (instead of WHERE clauses) into queries to speed them up, however, I am not really sure how I would do that or in what way this would work in this case.

    I've tried simply substituting the WHERE CLAUSE FOR
    Code:
    INNER JOIN X Main ON Tmp1.document_id = Main.document_id
    but I get the following error message:
    "At most one record can be returned by this subquery."

    Maybe someone knows how I would go about doing this or even has a completely different suggestion?


    2. The query won't run unless I assign the original table an alias ("Main" in the code above). Can someone explain why this is so essential?

    I'd really appreciate any help since I'm totaly new to Access and SQL. I've been progressing at a snail's pace with this and seem to be finally stuck after days of scrutinizing forums all over the place.

    Sorry if this has been discussed before or if I left out any essential context...

    Regards,
    Peter

    Thanks in advance, again, for ANY help!
    Last edited by ElPedroMagico; 08-28-14 at 17:05.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    SELECT A.Document_ID
         , A.CountOfDocuments
         , B.CountOfClass
         , C.CountOfItems
         , D.SumOfAmount
      FROM ((
    		(SELECT Document_ID, COUNT(Document_ID) AS CountOfDocuments
                       FROM Table_1
                   GROUP BY Document_ID
                     ) AS A INNER JOIN
                    (SELECT Document_ID, COUNT(Item_Class) AS CountOfClass
                       FROM Table_1
                   GROUP BY Document_ID
                    ) AS B ON A.Document_ID = B.Document_ID
                ) INNER JOIN
                    (SELECT Document_ID, COUNT(Item_No) AS CountOfItems
                       FROM Table_1
                   GROUP BY Document_ID
                    ) AS C ON A.Document_ID = C.Document_ID
           ) INNER JOIN
                    (SELECT Document_ID, SUM(Amount) AS SumOfAmount
                       FROM Table_1
                   GROUP BY Document_ID
                    ) AS D ON A.Document_ID = D.Document_ID;
    Note: When you post sample data, please do so in csv format, its easier to import in a test database (first row contains the column names):
    Code:
    Document_ID;Item_No;Item_Class;Amount
    1;1;X;10
    1;2;X;20
    1;3;Y;25
    2;1;Y;16
    2;2;Y;22
    2;3;Z;45
    2;4;X;40
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Sinndho View Post
    Like this:
    Note: When you post sample data, please do so in csv format, its easier to import in a test database (first row contains the column names):
    ...or better yet post the actual tables inside an Access DB with the columns defined as you actually use them.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2014
    Posts
    3
    Thank you, guys, for the quick response. Also thanks for the advice on posting.
    I attach the DB here (with table and query as suggested by Sinndho) and include the csv below.

    Code:
    "document_id";"class";"item_no";"amount"
    1;"a";1;12
    1;"b";2;13
    1;"b";3;43
    2;"a";1;12
    2;"c";2;54
    3;"a";1;322
    3;"c";2;21
    3;"b";3;65
    4;"c";1;342
    4;"c";2;165
    4;"b";3;765
    4;"a";4;34
    As for the suggested query code, it is very helpful, indeed, in terms of how to use inner joins. The only thing that it still doesn't do for me is count the distinct classes of items within the document.

    E.g., in the attached DB, I'd like the CountOfClass to be 2 for document 1 and 3 for document 4. If I just count the document_ids, item_nos and classes grouped by document_id, I get the same count for all attributes, since every record represents an item.

    I hope this was a concise, but still clear enough, explanation.

  5. #5
    Join Date
    Aug 2014
    Posts
    3
    Thank you, guys, for the quick response. Also thanks for the advice on posting.
    I attach the DB here (with table and query as suggested by Sinndho) and include the csv below.

    Code:
    "document_id";"class";"item_no";"amount"
    1;"a";1;12
    1;"b";2;13
    1;"b";3;43
    2;"a";1;12
    2;"c";2;54
    3;"a";1;322
    3;"c";2;21
    3;"b";3;65
    4;"c";1;342
    4;"c";2;165
    4;"b";3;765
    4;"a";4;34
    As for the suggested query code, it is very helpful, indeed, in terms of how to use inner joins. The only thing that it still doesn't do for me is count the distinct classes of items within the document.

    E.g., in the attached DB, I'd like the CountOfClass to be 2 for document 1 and 3 for document 4. If I just count the document_ids, item_nos and classes grouped by document_id, I get the same count for all attributes, since every record represents an item.

    I hope this was a concise, but still clear enough, explanation.
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno where to start to be honest
    all I can see is the end product of your slow query/queries not the actual tables

    one thign you can do in Access is build separate queries which do the sub tasks

    so you could have a query for each summation that uses different critieria, which returns the summation (COUNT or SUM) + something that ties back to the parent query, then join on that tieback
    I'd rather be riding on the Tiger 800 or the Norton

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
  •