using group by operators in sql

Question

i have two columns - email id and customer id, where an email id can be associated with multiple customer ids. Now, I need to list only those email ids (along with their corresponding customer ids) which are having a count of more than 1 customer id. I tried using grouping sets, rollup and cube operators, however, am not getting the desired result.

Any help or pointers would be appreciated.

What concrete RDBMS is this for? Please add a relevant tag - such as `oracle`, `mysql`, `postgresql`, `db2`, `sql-server`, `interbase` or whatever else you might be using — marc_s, Jul 22 '15 at 20:23
possible duplicate of [SQL: Using group by and having clause](http://stackoverflow.com/questions/16327954/sql-using-group-by-and-having-clause) — Tab Alleman, Jul 22 '15 at 20:33

score 2 · Answer 1 · edited Jul 22 '15 at 20:47

2

SELECT emailid
FROM 
(  SELECT emailid, count(custid)
   FROM table
   Group by emailid
   Having count(custid) > 1
)

edited Jul 22 '15 at 20:47

Adam

2,422
18
29

answered Jul 22 '15 at 20:28

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

If you need a comma separated list of all of their customer id's returned with the single email id, you could use GROUP_CONCAT for that.

This would find all email_id's with at least 1 customer_id, and give you a comma separated list of all customer_ids for that email_id:

SELECT email_id, GROUP_CONCAT(customer_id)
FROM your_table
GROUP BY email_id
HAVING count(customer_id) > 1;

Assuming email_id #1 was assigned to customer_ids 1, 2, & 3, your output would look like:

email_id | customer_id
1        | 1,2,3

I didn't realize you were using MS SQL, there's a thread here about simulating GROUP_CONCAT in MS SQL: Simulating group_concat MySQL function in Microsoft SQL Server 2005?

edited May 23 '17 at 12:23

Community

1
1

answered Jul 22 '15 at 20:24

Ben Fried

2,168
1
14
13

in that case i won't be getting the customer id's...it only lists the emails and their count – PratikGandhi Jul 22 '15 at 20:26
See GROUP_CONCAT that I posted. – Ben Fried Jul 22 '15 at 20:27
is there anything similar for ms sql server? – PratikGandhi Jul 22 '15 at 20:31
Oh, I didn't realize you were using MS SQL, let me look – Ben Fried Jul 22 '15 at 20:31
yeah i saw that as well...it would be way more complicated than i was hoping for...in any case, Kevin's solution worked for me...thanks for your help – PratikGandhi Jul 22 '15 at 20:40

score 0 · Answer 3 · answered Jul 22 '15 at 20:26

0

Sounds like you would need to use HAVING

e.g

SELECT email_id, COUNT(customer_id)
From sometable
GROUP BY email_id
HAVING COUNT(customer_id) > 1

HAVING allows you to filter following the grouping of a particular column.

answered Jul 22 '15 at 20:26

Mat Richardson

3,576
4
31
56

score 0 · Answer 4 · answered Jul 22 '15 at 20:28

WITH email_ids AS (
    SELECT email_id, COUNT(customer_id) customer_count
    FROM Table
    GROUP BY email_id
    HAVING count(customer_id) > 1
)

SELECT t.email_id, t.customer_id
FROM Table t
INNER JOIN email_ids ei
    ON ei.email_id = t.email_id

score 0 · Accepted Answer · answered Jul 22 '15 at 20:28

0

I think this will get you what you want, if I am understanding you question correctly

select emailid, customerid from tablename where emailid in 
(
select emailid from tablename group by emailid having count(emailid) > 1
)

answered Jul 22 '15 at 20:28

Kevin

7,162
11
46
70

this did it for me...didn't realize it would be that simple...was unnecessarily complicating things using group by operators – PratikGandhi Jul 22 '15 at 20:38

MADnoobie · Answer 6 · 2015-07-22T20:34:34.497

SELECT t1.email, t1.customer
FROM table t1 
INNER JOIN (
    SELECT email, COUNT(customer)
    FROM table
    GROUP BY email
    HAVING COUNT(customer)>1
    ) t2 on t1.email = t2.email

This should get you what your looking for. Basically, as other ppl have stated, you can filter group by results with HAVING. But since you want the customerids afterwards, join the entire select back to your original table to get your results. Could probably be done prettier but this is easy to understand.

score 0 · Answer 7 · answered Jul 22 '15 at 20:39

SELECT 
    email_id,
    STUFF((SELECT ',' + CONVERT(VARCHAR,customer_id) FROM cust_email_table T1 WHERE T1.email_id = T2.email_id
          FOR
           XML PATH('')
          ),1,1,'') AS customer_ids
FROM
    cust_email_table T2
GROUP BY email_id
HAVING COUNT(*) > 1

this would give you a single row per email id and comma seperated list of customer id's.

using group by operators in sql

7 Answers7