Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: count

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: count

    I have two tables that were imported in from two different sources. Table one has two fields labeled Dept # and Count and table two has four fields labeled Dept #; SubDept1; SubDept2 and SubDept3. The field Dept # in table one includes all sub dept from table two in one field labeled Dept #. For example:

    Table One
    Dept # Count
    1000 4590
    1001 7456
    1002 5465
    1003 2657
    2000 5644
    2001 8458
    2002 1555
    2003 3256

    Table Two
    Dept# SubDept1 SubDept2 SubDept3
    1000 1001 1002 1003
    2000 2001 2002 2003

    I need to get one total for each Dept # which includes all associated sub depts. I have two many depts. to do this manually. Can anyone tell me a better way to do this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select TableTwo.Dept
         , Sum(TableOne.Count) as SumCount
      from TableOne
    inner
      join TableTwo
        on TableOne.Dept in (TableTwo.Dept
                            ,TableTwo.SubDept1
                            ,TableTwo.SubDept2
                            ,TableTwo.SubDept3)
    group
        by TableTwo.Dept
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    214

    count

    Thank you for responding. I ran the code and getting an error: Syntax Error(Missing Operator) in query expression.

    I use the code line by line only change the name of the tables. I checked and double checked.
    What's wrong please?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    mebbe you should post the SQL you are using....
    its often very difficult to diagnose problems without actually seeing the code that is reporting the problems
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    mebbe you should post the SQL you are using....
    its often very difficult to diagnose problems without actually seeing the code that is reporting the problems
    I don't think you need to get far beyond Rudy's code to see the problem....

    Wassssap with that Rudy?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Wassssap with that Rudy?
    what's up with rudy? well, right now he's getting ready to fry up a nice pork chop

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    what's up with rudy? well, right now he's getting ready to fry up a nice pork chop

    Punctuation pedant
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2004
    Posts
    214

    count

    ok. see attachment
    Thanks
    Last edited by slimjen; 07-05-13 at 23:09.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    slimjen, please just post the code of your query in plain text, not your entire database

    for one thing, some of us cannot open it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2004
    Posts
    214
    sorry.

    Code:
    SELECT tbl_DeptNumber.TXT_Dept, Sum( AandB.TXT_NUMCount) as SumCount
    FROM AandB inner join tbl_DeptNumber on AandB.TXT_Dept in (tbl_DeptNumber.TXT_Dept, tbl_DeptNumber.TXT_SubDept1, tbl_DeptNumber.TXT_SubDept2, tbl_DeptNumber.TXT_SubDept3) group by  tbl_DeptNumber.TXT_Dept;
    Thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sigh stupid, stupid microsoft access

    try it this way, please
    Code:
    SELECT tbl_DeptNumber.TXT_Dept
         , SUM( AandB.TXT_NUMCount) AS SumCount
      FROM AandB 
         , tbl_DeptNumber 
     WHERE AandB.TXT_Dept in (tbl_DeptNumber.TXT_Dept
                            , tbl_DeptNumber.TXT_SubDept1
                            , tbl_DeptNumber.TXT_SubDept2
                            , tbl_DeptNumber.TXT_SubDept3) 
    GROUP 
        BY tbl_DeptNumber.TXT_Dept;
    why is the column named TXT_NUMCount? is it a text field? if so, good luck summing it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2004
    Posts
    214

    count

    the Num_Count field is number. Sorry; I didn't name the fields. I ran the code again and no go. You took the join out. Should it not have the join between the tables? I must be missing something. I'll try again.

  13. #13
    Join Date
    Jul 2004
    Posts
    214

    count

    When I run this, it gives me only the count from the main dept and doesn't pick up the counts for the sub depts.

    Code:
    SELECT tbl_DeptNumber.TXT_Dept, Sum([tbl_AandB].[NUM_COUNT]) AS SumCount
    FROM tbl_AandB INNER JOIN tbl_DeptNumber ON tbl_AandB.TXT_Dept = tbl_DeptNumber.TXT_Dept
    WHERE (((tbl_AandB.TXT_Dept) In ([tbl_DeptNumber].[TXT_Dept],[tbl_DeptNumber].[TXT_SubDept1],[tbl_DeptNumber].[TXT_SubDept2],[tbl_DeptNumber].[TXT_SubDept3])))
    GROUP BY tbl_DeptNumber.TXT_Dept;
    Thanks

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slimjen
    When I run this...
    that's an incorrect join

    try the query in post #11

    when i run it on the data that you supplied, i get the following results:

    dept sumcount
    1000 20168
    2000 18913

    is this correct or not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2004
    Posts
    214
    Yes. This is exactly what I want. I must be leaving something out. Let me try again.
    Thanks

Posting Permissions

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