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

    Unanswered: combine these two sql statements

    SELECT bms_id,email_address,COUNT(*)
    INTO #temp
    FROM emp_db
    WHERE email_address IS NOT NULL
    GROUP BY bms_id,email_address
    ORDER BY bms_id DESC,COUNT(*) DESC

    SELECT bms_id COUNT(*)
    FROM #TEMP
    GROUP BY bms_id
    ORDER BY COUNT(*) DESC

    How can i put these two statements into a single sql statement.

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    in the FROM part of the code, you could embed the second select statement.

    maybe use a stored procedure or view as the source for a second TABLE IN THE FROM PART

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about derived table? I did not test this query but try this idea...

    SELECT q.bms_id, COUNT(*)
    (SELECT bms_id,email_address,COUNT(*) as f1
    FROM emp_db
    WHERE email_address IS NOT NULL
    GROUP BY bms_id,email_address) as q
    GROUP BY q.bms_id
    ORDER BY COUNT(*) DESC

    From BOL:

    USE pubs
    SELECT RTRIM(a.au_fname) + ' ' + LTRIM(a.au_lname) AS Name, d1.title_id
    FROM authors a, (SELECT title_id, au_id FROM titleauthor) AS d1
    WHERE a.au_id = d1.au_id
    ORDER BY a.au_lname, a.au_fname
    Last edited by snail; 06-02-04 at 11:47.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    This will count the number of distinct emails per id. Is this the requirement?

    SELECT bms_id, COUNT(DISTINCT Email)
    FROM emp
    WHERE Email IS NOT NULL
    GROUP BY bms_id
    ORDER BY 2 DESC

Posting Permissions

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