Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2010
    Posts
    9

    Unanswered: SQL Query Help !

    Hi i am using the below query to get the data as a cross tab :

    Code:
      SELECT   closedate,status,
               SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
               SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
               SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
               SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
               FROM   Module_Fixed
       WHERE   status = 'Fixed' or status = 'Already_Fixed' or status = 'Error_Not_In_DB' or status = 'Not_An_Error' or status = 'Partially_Fixed'
               GROUP BY   closedate, status

    I would like to get data from 3 tables from a single query where modulecode should aslo be sum from all the 3 tables.

    where condition for the second table :

    Code:
     status = 'Fixed' or status = 'Already_Fixed' or status = 'Error_Not_In_DB'
    where condition for the third table

    Code:
    status = 'Not_An_Error' or status = 'Partially_Fixed'
    I tried in the same query by giving all 3 tables , however the query is running forever .

    Thank you

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you have indexes for the status column in all three tables? Also, have you tried using IN clause rather than AND/OR. One other item that may help would be if you were to sum up each of the individual tables first, using a UNION ALL. Something like:

    Code:
    select closedate,status,
               SUM (b."1.1")
              ,SUM (b."1.2")
              ,SUM (b."1.3")
              ,SUM (b."1.4")
       from (SELECT   closedate,status,
               SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
               SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
               SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
               SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
               FROM   Module_Fixed
       WHERE   status IN ( 'Fixed' , 'Already_Fixed' , 'Error_Not_In_DB' , 'Not_An_Error' , 'Partially_Fixed')
               GROUP BY   closedate, status
    union all
              SELECT   closedate,status,
               SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
               SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
               SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
               SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
               FROM   next table
       WHERE   status IN ( 'Fixed' , 'Already_Fixed' , 'Error_Not_In_DB')
               GROUP BY   closedate, status
    union all
               SELECT   closedate,status,
               SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
               SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
               SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
               SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
               FROM   Module_Fixed
       WHERE   status IN ( 'Not_An_Error', 'Partially_Fixed')
               GROUP BY   closedate, status) as b
    GROUP BY   closedate, status
    Dave

  3. #3
    Join Date
    May 2008
    Posts
    277
    In addition to being a little simpler, I imagine this might also be faster, since all the grouping and summing is done only once. Try them both and see what works.

    Code:
    select
        closedate,
        status,
        SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
        SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
        SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
        SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
    from (
            SELECT
                closedate,
                status,
                modulecode
            FROM table1
            WHERE status IN ( 'Fixed' , 'Already_Fixed' , 'Error_Not_In_DB' , 'Not_An_Error' , 'Partially_Fixed')
            union all
            SELECT
                closedate,
                status,
                modulecode
            FROM table2
            WHERE status IN ( 'Fixed' , 'Already_Fixed' , 'Error_Not_In_DB')
            union all
            SELECT
                closedate,
                status,
                modulecode
            FROM table3
            WHERE status IN ( 'Not_An_Error', 'Partially_Fixed')
        ) as statii
    GROUP BY closedate, status

  4. #4
    Join Date
    Jul 2010
    Posts
    9
    Thanks , i appreciate the response by all.

    Dave , There is no index on the statue column.

    I will check the result of the queries provided and post the result.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Your performance should be much improved regardless. I think your original attempt was, probably, creating a cartesian product, which would have ended up giving you the incorrect results as well.
    Dave

  6. #6
    Join Date
    Jul 2010
    Posts
    9
    Thanks a lot Dave and Futurity, the query is working perfectly as i intended.

    I would like to know is there a way i can avoid manually providing values of "Modulecode" for the summation ? since there are many in my table

    Thanks a lot.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you mean:
    Code:
    select  closedate
             ,status
             ,modulecode
             ,count(*)
        from ....
    where... 
    group by closedate
             ,status
             ,modulecode
    ?
    Dave

  8. #8
    Join Date
    Jul 2010
    Posts
    9
    Hi Dave

    as for the following query i am mentioning sum for each modulecode 1.1,1.2 ....

    How can i avoid mentioning each value instead get all that from table.

    Code:
    select
        closedate,
        status,
        SUM (CASE WHEN modulecode = '1.1' THEN 1 ELSE 0 END) AS "1.1",
        SUM (CASE WHEN modulecode = '1.2' THEN 1 ELSE 0 END) AS "1.2",
        SUM (CASE WHEN modulecode = '1.3' THEN 1 ELSE 0 END) AS "1.3",
        SUM (CASE WHEN modulecode = '1.4' THEN 1 ELSE 0 END) AS "1.4"
    from (......
    Thanks

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    did you see my post above???
    Dave

  10. #10
    Join Date
    Aug 2010
    Posts
    1
    Could any one give me a better idea to obtain data from databases.
    I obtain these data through a combination of SQL queries and spreadsheet manipulation.
    So far, I don't know how to do by a efficient way to design a SQL query to fetch the data directly.
    it's a big table and with huge column

  11. #11
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by dav1mo View Post
    did you see my post above???
    Dave
    I think he means something like the crosstab function from the tablefunc module, which automatically generates column headers for you.

    I'm not too familiar with that function, so I don't know if it's capable of handling more complicated queries like his.

  12. #12
    Join Date
    Jul 2010
    Posts
    9
    Please let me tell what i need exactly :

    The result of the as per the query (posted by dave previously) is below , we have 10 for the modulecode 1.1 against the date 2009-12-16

    Code:
     Closedate  Modulecode1.1 Modulecode1.2 
    2009-12-16    10               12 
    2010-01-08    3                 2
    Now , In all the tables we have a column "EMPCODE" . Suppose there are 3 employees worked on 1.1 module , the result should be 3 for that particular module

    For Example the result should be like this (Based on Empcode Worked on particular Module):
    Code:
     Closedate  No_of_emp_Worked_on_1.1 No_of_emp_Worked_on_1.2 
    2009-12-16    3                        2 
    2010-01-08    1                        1
    I hope i explained properly.

    Thanks

  13. #13
    Join Date
    May 2008
    Posts
    277
    Code:
    select  closedate
             ,status
             ,modulecode
             ,count(distinct empcode)
        from ....
    where... 
    group by closedate
             ,status
             ,modulecode
    ?

Posting Permissions

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