Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Newbie SQL Question

    Hi, I am new here. i need help with a simple sql query, hard to me though.

    I have the following sql:

    Code:
    Select (1+ len(phonelist)-len(replace(phonelist,' ','')))*  count (*)
    from systems
    where orders in ('process', 'Order')
    and maildate = '20100218'
    and code IN ('abc', 'deg')
    group by phonelist
    It counts the number of words in the phonelist column and then multiples it by the count of the number of times it appears.

    Instead of having something like:

    motorola 5
    LG 10
    Samsung 14

    How can i just have the sum of everything, so it shows just 29. Also how can I layer in a select statement that will only count the phonelist when it is populated because I have some blank phonelist records.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mzsuga View Post
    How can i just have the sum of everything, so it shows just 29.
    remove the GROUP BY clause


    Quote Originally Posted by mzsuga View Post
    Also how can I layer in a select statement that will only count the phonelist when it is populated because I have some blank phonelist records.
    replace phonelist in the query with
    Code:
    NULLIF(TRIM(phonelist),'')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by r937 View Post
    remove the GROUP BY clause


    replace phonelist in the query with
    Code:
    NULLIF(TRIM(phonelist),'')

    I get an error:

    'TRIM' is not a recognized function name. So i replaced Trim with LTRIM but if I remove the group by it will say is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

    Also it is still showing the blanks phonelist, it's just replacing it with NULL but it would still ahve a value to it since it's still counting it. How can I show jsut the phonelist with something populated?


    Code:
    select  sum((1+len(NULLIF(LTRIM(phonelist),''))-len(replace(NULLIF(LTRIM(phonelist),''),' ','')))) *  count (*)
    systems
    where orders in ('process', 'Order')
    and maildate = '20100218'
    and code IN ('abc', 'deg')
    So i got rid of the group by, but it is summing up the ones with the blank phonelist as well.
    Last edited by mzsuga; 03-01-10 at 16:51.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

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