Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: can we combine these 3 statements into one single query

    SELECT 1 as id,COUNT(name) as count1
    INTO #temp1
    FROM emp

    SELECT 1 as id,COUNT(name) as count2
    INTO #temp2
    FROM emp
    WHERE name <>' ' AND name IS NOT NULL OR name <> NULL


    SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
    FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT 1 as id,COUNT(name) as count1
    INTO #temp1
    FROM emp
    UNION ALL
    SELECT 2 as id,COUNT(name) as count2
    INTO #temp2
    FROM emp
    WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
    UNION ALL
    SELECT 3 as id, (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
    FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
    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
    Apr 2003
    Posts
    176
    The above query doesn't work for me and infact i want to get the percentage in a single query..
    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm "winging" this one wildly, but could you use:
    Code:
    SELECT
       CAST(Sum(CASE WHEN name IS NOT NULL
          AND name <> '' THEN 1 END) AS FLOAT) / Count(*)
       FROM dbo.emp
    This divides the number of names with value by the total to get the percentage of usable names.

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about

    Code:
    SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
    FROM (
    SELECT COUNT(name) as count1
    INTO #temp1
    FROM emp
    JOIN
    SELECT COUNT(name) as count2
    INTO #temp2
    FROM emp
    WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
    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.

  6. #6
    Join Date
    Apr 2003
    Posts
    176
    SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
    FROM (
    SELECT COUNT(name) as count1
    INTO #temp1
    FROM emp
    JOIN
    SELECT COUNT(name) as count2
    INTO #temp2
    FROM emp
    WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
    ...............................

    I could not execute the above query, can we write the query like that?
    please correct me if i am wrong?

  7. #7
    Join Date
    Oct 2003
    Posts
    84
    Don't use Name <> NULL

    No value will ever match that:

    MyField > NULL will always be false
    MyField < NULL will always be false
    MyField <> NULL will always be false
    MyField = NULL will always be false
    and so on for all applicable operators...

    try using:

    NULLIF(Name, TRIM(Name)) IS NOT NULL

    to catch fields containing only spaces.

  8. #8
    Join Date
    Oct 2003
    Posts
    84
    Quote Originally Posted by afx2029
    try using:

    NULLIF(Name, TRIM(Name)) IS NOT NULL

    to catch fields containing only spaces.

    My bad. Use this instead:

    NULLIF('', TRIM(Name)) IS NOT NULL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but didn't my suggestion work? What results did it produce?

    -PatP

  10. #10
    Join Date
    May 2004
    Posts
    3

    use ISNULL() function

    Replace:
    WHERE name <>' ' AND name IS NOT NULL OR name <> NULL) AS XXX
    By:
    WHERE LTRIM(ISNULL(name,'')) <> '' ) AS XXX

    yabu.

Posting Permissions

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