0

i am using mysql. I have a table called zim_new_usa I want a list of all emails which are of type aol and gmail the table has a column called office state

there are 50 states in USA i want 500 emails from each state. I don't know how to write group function in query. the query i tried to use is 

select *  from zim_new_usa where 
(email like '%@aol%' or email like '%@gmail%') and length(`office state`) = 2
mb1987
  • 437
  • 8
  • 21

2 Answers2

1

Have a look here:

Using LIMIT within GROUP BY to get N results per group?

You need a Group by clause for the email and for the office_state fields.

Community
  • 1
  • 1
alexstdev
  • 52
  • 9
  • I want 500 emails from each state, If i do group by email and limit 25000, it does not give me right answer, it is possible the query fetch more than 500 for a state. – mb1987 Feb 19 '15 at 21:18
0

Have no possibility to debug, But try this:

SET @num := 0, @state:= '';

SELECT t.email, t.`office state`, 
      @num := if(@state = t.`office state`, @num + 1, 1) AS row_number,
      @state := t.`office state` AS dummy
FROM (
  SELECT email, `office state` 
  FROM zim_new_usa 
  WHERE 
  (email LIKE '%@aol%' OR email LIKE '%@gmail%') 
 AND length(`office state`) = 2 
 ORDER BY `office state`
) as t
GROUP BY t.`office state`,t.email
HAVING row_number <= 5;

By the way, try to avoid using spaces when title table or column.

Alex
  • 16,739
  • 1
  • 28
  • 51
  • Respect it worked, thanks you very much. now i am changing row_number <= 500. to get the right answer. Honestly you should take my job :) – mb1987 Feb 19 '15 at 22:26