Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Using COUNT to add fields in a report

    If I am totaling fields by groups of rows and I do so for every group do I need to use a stored procedure or cursor for this? I don't have a lot of experience with these areas but will give it a go based on what I find out.

    Let me try to provide an example.
    Code:
    BranchNo    OrderNo    ErrorCode1    ErrorCode2    ErrorCode3
      478             111               0                  1                  1
      478             112               0                  0                  0
      478             113               1                  0                  0
      610             119               0                  0                  0
      610             120               1                  0                  0
    I am trying to total the "error code" fields for each Branch. Of course, some don't have any, some have multiple errors. If a stored procedure is the only way, it will be a problem as our company's DBA has not given me permissions to run SPROCs. Is there a way to do this in a query?

    I have been trying to figure out a subquery for this and it is not working.

    ddave

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    dba won't let you execute sprocs?

    That's all I'd let you do...


    You looking for this?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(BranchNo int,OrderNo int,ErrorCode1 int,ErrorCode2 int,ErrorCode3 int)
    GO
    
    INSERT INTO myTable99(BranchNo,OrderNo,ErrorCode1,ErrorCode2,ErrorCode3)
    SELECT 478, 111, 0, 1, 1 UNION ALL
    SELECT 478, 112, 0, 0, 0 UNION ALL
    SELECT 478, 113, 1, 0, 0 UNION ALL
    SELECT 610, 119, 0, 0, 0 UNION ALL
    SELECT 610, 120, 1, 0, 0
    GO
    
      SELECT BranchNo, SUM(ErrorCode1), SUM(ErrorCode2), SUM(ErrorCode3)
        FROM myTable99
    GROUP BY BranchNo
    GO
    
    DROP TABLE myTable99
    GO
    Which should be in a sproc...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    Re: Using COUNT to add fields in a report

    create table #temp (
    BranchNo int,
    OrderNo int,
    ErrorCode1 int,
    ErrorCode2 int,
    ErrorCode3 int)

    insert into #temp values ( 478, 111, 0, 1, 1)
    insert into #temp values ( 478, 112, 0, 0, 0)
    insert into #temp values ( 478, 113, 1, 0, 0)
    insert into #temp values ( 610, 119, 0, 0, 0)
    insert into #temp values ( 610, 120, 1, 0, 0)
    create table #temp (
    BNo int,
    ONo int,
    ECode1 int,
    ECode2 int,
    ECode3 int)

    insert into #temp values ( 478, 111, 0, 1, 1)
    insert into #temp values ( 478, 112, 0, 0, 0)
    insert into #temp values ( 478, 113, 1, 0, 0)
    insert into #temp values ( 610, 119, 0, 0, 0)
    insert into #temp values ( 610, 120, 1, 0, 0)

    select BNo, ONo, ECode1, ECode2, ECode3, (ECode1+ECode2+ECode3) ECodeSum
    from #temp

    drop table #temp

    Results:
    BNo ONo ECode1 ECode2 ECode3 ECodeSum
    ----- ---- ------ ------ ------ --------
    478 111 0 1 1 2
    478 112 0 0 0 0
    478 113 1 0 0 1
    610 119 0 0 0 0
    610 120 1 0 0 1

    (5 row(s) affected)

    Originally posted by dolfandave
    If I am totaling fields by groups of rows and I do so for every group do I need to use a stored procedure or cursor for this? I don't have a lot of experience with these areas but will give it a go based on what I find out.

    Let me try to provide an example.
    Code:
    BranchNo    OrderNo    ErrorCode1    ErrorCode2    ErrorCode3
      478             111               0                  1                  1
      478             112               0                  0                  0
      478             113               1                  0                  0
      610             119               0                  0                  0
      610             120               1                  0                  0
    I am trying to total the "error code" fields for each Branch. Of course, some don't have any, some have multiple errors. If a stored procedure is the only way, it will be a problem as our company's DBA has not given me permissions to run SPROCs. Is there a way to do this in a query?

    I have been trying to figure out a subquery for this and it is not working.

    ddave

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    OK,

    Now here is one thing I overlooked. That works for the fields where the error codes are 0 and 1 but only IF the error code is 1. In other words, and I have to look, if the error code is 0 and there are 5 records with this error code, I won't be able to SUM the records and wind up with 5, which is the correct answer.

    Also, I see in my first error code field, whoever created the records created error codes 1 & 2. Why that is I don't know. But the issue is with values of both 1 & 2 in this field I won't be able to do what you suggest. I thought of taking the total of the 2's and divide them by 2 but if there are 2 values of 1 then this approach won't work. Sorry I wasn't more specific.

    ddave


    Originally posted by Brett Kaiser
    dba won't let you execute sprocs?

    That's all I'd let you do...


    You looking for this?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(BranchNo int,OrderNo int,ErrorCode1 int,ErrorCode2 int,ErrorCode3 int)
    GO
    
    INSERT INTO myTable99(BranchNo,OrderNo,ErrorCode1,ErrorCode2,ErrorCode3)
    SELECT 478, 111, 0, 1, 1 UNION ALL
    SELECT 478, 112, 0, 0, 0 UNION ALL
    SELECT 478, 113, 1, 0, 0 UNION ALL
    SELECT 610, 119, 0, 0, 0 UNION ALL
    SELECT 610, 120, 1, 0, 0
    GO
    
      SELECT BranchNo, SUM(ErrorCode1), SUM(ErrorCode2), SUM(ErrorCode3)
        FROM myTable99
    GROUP BY BranchNo
    GO
    
    DROP TABLE myTable99
    GO
    Which should be in a sproc...

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is a zero always a zero, or does zero mean one thing in Errorcode1 and something different in Errorcode3?

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think you want this

    Code:
      SELECT BranchNo, COUNT(ErrorCode1), COUNT(ErrorCode2), COUNT(ErrorCode3)
        FROM myTable99
    GROUP BY BranchNo
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    193
    Hi Pat,

    I think I learned this from you. I ran an EXECUTE sp_help on the table. The results:
    Code:
    column_name            type     computed    length    nullable
    phase_code_bad	bit	no	1	no
    close_code_bad	bit	no	1	no
    branch_num_bad	bit	no	1	no
    agent_num_bad	bit	no	1	no
    agent_name_bad	bit	no	1	no
    A zero then would mean a good record. A 1 a bad record. This is the case in most of the fields except one I have seen so far where 1=good and 2=bad.

    ddave

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I got a novel idea...

    why don't you show us what the results should look like...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Posts
    193
    Yes, a picture is worth a 1000 words:

    Code:
    code:----------------------------------------------------------------------------
    BranchNo    OrderNo    ErrorCode1    ErrorCode2    ErrorCode3
      478             111               0                  1                  1
      478             112               0                  0                  0
      478             113               1                  0                  0
      610             119               0                  0                  0
      610             120               1                  0                  0
    --------------------------------------------------------------------------------
    would realistically be more like:

    Code:
    code:--------------------------------------------------------------------------
    BranchNo    ErrorCode1    ErrorCode2    ErrorCode3
      478                 42                  31                 19
      610                 18                    0                 15
    --------------------------------------------------------------------------------
    So this would be representative of the totals per branch. That is what I really need.

    ddave

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because of your 0, 1 versus 1, 2 scenario, you'll have to be a bit more tricky, but I'd use:
    PHP Code:
    SELECT branchno
    ,  Coalesce(Sum(CASE WHEN errorcode1 <> 0 THEN 1 END), 0) AS errorcode1
    ,  Coalesce(Sum(CASE WHEN errorcode2 <> 0 THEN 1 END), 0) AS errorcode2
    ,  Coalesce(Sum(CASE WHEN errorcode1 <> 1 THEN 1 END), 0) AS errorcode3
       FROM dbo
    .myTable
       GROUP BY brancho
       ORDER BY brancho 
    That is assuming that errorcode3 is the column with the 1, 2 scenario.

    Edited out the orderno, it was meaningless in this context

    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ok now explain, how for branch 478, errorcode1 gets a 42...

    the data doesn't support it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    ok now explain, how for branch 478, errorcode1 gets a 42...

    the data doesn't support it...
    I'm assuming that DolfanDave didn't give us all the data, just a representative sample. You can't get there from here.

    -PatP

  13. #13
    Join Date
    Feb 2004
    Posts
    193
    Sorry, guys, still a rookie here.

    Branch 478's total ErrorCode 1's will SUM to 42, just as an example. So 1+1+1+1+...42 times in other words.

    All the fields need to be added up by Branch Numbers. There are about 1000 branches and we need totals across approx 12 fields(columns) for all the branches.

    ddave

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The code snippet that I posted should work, but beware of the 1, 2 combinations!

    -PatP

  15. #15
    Join Date
    Feb 2004
    Posts
    193
    OK,

    Time for more "fiddling". Thanks again, you guys are fantastic.

    ddave

Posting Permissions

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