mysql return row for each field column count value

Question

I am not sure how to ask the question so I am not sure what i am looking for. Here is what I am trying to do.

My table has zipcodes and a counter column

 id  zipcode count
 11  50022   23
 12  50023   1
 13  50025   8
 14  50042   2
 15  50076   2

I need to have a select statement that returns the zipcodes X the amount in the counter column (integer value).

So the select return looks like this:

50022
50022
50022
50022
50022
50022 (and on 23 times)
50023 (one record here)
50025 (there were eight of these so eight records).

and so on.

Is there a function or grouping of functions that can achieve this in MySQL?

This is part of a larger query so I can't handle this in server-side code (otherwise I would just create a loop).

Hope all this makes sense and thanks in advance!

UPDATE: I am not trying to get a distinct grouping ...doing the opposite (as one person commented below).

You should set up an SQLFiddle and show us what you have already tried. — DevlshOne, Jul 26 '13 at 18:45

Ahmed Habib · Answer 1 · 2013-07-26T18:56:37.090

0

This can be what you want. Though I have not understood your question quite well..

    $q = mysql_query("SELECT count FROM <table> WHERE zipcode='50022'");

    while($data = mysql_fetch_array($q)){
    echo "The {$data['zipcode']} is {$data['count']} times printed.";
    for($i=1;$i<=$data['count'];$i++){
      echo $i." - ".$data['zipcode'];
}
    }

UPDATE:

if you are trying to do this inside mysql try this in SQL query

SELECT count FROM <table> WHERE zipcode='50022'

<table> change with your table name.

edited Jul 26 '13 at 18:56

answered Jul 26 '13 at 18:44

Ahmed Habib

189
11

This looks like PHP code (I am doing this in a view inside MySQL). I am not trying to get the count value. – Frank Tudor Jul 26 '13 at 18:57

score 0 · Answer 2 · answered Jul 26 '13 at 18:45

0

I think what you are looking for is the sql group by clause. This clause allows you to group your data by a certain qualifier and is added into your select statement.

So if I understand correctly and you are grouping your zipcodes by the count column you would do something along the lines of:

SELECT zipcode FROM [table_name] GROUP BY count;

I hope this helps.

answered Jul 26 '13 at 18:45

Tcarp

1
4

I think you misunderstood the question. He wants the inverse of a GROUP BY. There's only one entry for each zip code. He wants the zip code to appear a certain numbers of times. Example, first line, the column "count" is 23 so he wants that zip code to appear 23 times. – Y0lk Jul 26 '13 at 18:49
Ah, then yes I misunderstood. Sorry about that. – Tcarp Jul 26 '13 at 18:55

score 0 · Answer 3 · edited May 23 '17 at 10:32

Create a dummy table as given below

CREATE TABLE `dummy1` (
  `dummyId` int(11) NOT NULL,
  PRIMARY KEY  (`dummyId`)
) 

insert into dummy1 values(1),(2),(3), (4), (5),.......(n);

where n being is fairly big number

after that use he below query to populate the result

select zipcode from yourtable, dummy1 where countValue <= dummyId order by zipcode

I know this is not a neat solution. But since mysql does not support connect by as oracle does, this seems to be a solution to overcome this kind of situations

For similar question, Refer This

mysql return row for each field column count value

3 Answers3