I have 2 tables books and bkdates I want to get duplicates according to count books id|name|edno 1|book1|1 2|book2|2 3|book1|1 4|book2|2 5|book1|1 6|book3|3 -------------------------------- bkdates edno|year 1|1980 2|1990 1|1980 1|1988 2|1991 2|1990 1|1980 3|2003 ----------------------------------------------------------- expected output edno|year|count 1|1980|3 1|1988|1 2|1990|2 2|1991|1 any help??!
Uhm - with the data you've provided, that's not possible - you have no distinction in the tables on what you need to connect them on. Let me explain: The first table, "books", have 3 columns: id, name, edno The second table, "bkdates" have 2 columns: edno, year There is no way you can match the second-table edno to the first table edno, with the information in those tables - edno = 1 in the first table will match the first edno = 1 in the second table - you won't get any different years out of it. For that you'll have to rethink your table-structure.
It appears some of the data is redundant, but you can do something like (note I haven't tried this but something like this should work): select *, count(*) from bkdates group by edno -- or -- group by year