Re: [sqlite] Select names where occurences are greater than 10
That's brilliant thank you very much :clap: Adam Black, Michael (IS) wrote: > > select id,count(distinct track) from tracks group by id having > count(distinct track)>=10; > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of chiefmccrossan [adammccros...@gmail.com] > Sent: Thursday, July 21, 2011 2:15 PM > To: sqlite-users@sqlite.org > Subject: EXT :Re: [sqlite] Select names where occurences are greater than > 10 > > > Perfect! Thanks a million! =) > > Just wondering, is there anyway to select only unique results in what is > returned and no duplicates? > > For example, Elvis Presley has a track called Teddy Bear in my table 6 > times > but I only want to include it once? > > > luuk34 wrote: >> >> On 21-07-2011 18:59, chiefmccrossan wrote: >>> >>> I have a Tracks table containing columns for artistname and trackname. I >>> want >>> to be able to select all artist names that have more than ten tracks. >>> For >> >> SELECT artisname, count(tracks) >> FROM Tracks >> GROUP BY artistname >> HAVING count(tracks)>10 >> >> >> >>> example Elvis Presley has more than 20 tracks in the Tracks table but >>> Johnny >>> Cash has only 5 tracks. I need to be able to count Elvis and discount >>> Johnny >>> cash? >>> >>> Any idea how i can do this? >>> >>> Thanks >>> Adam >> >> >> -- >> Luuk >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > View this message in context: > http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32110258.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32116159.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select names where occurences are greater than 10
select id,count(distinct track) from tracks group by id having count(distinct track)>=10; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of chiefmccrossan [adammccros...@gmail.com] Sent: Thursday, July 21, 2011 2:15 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Select names where occurences are greater than 10 Perfect! Thanks a million! =) Just wondering, is there anyway to select only unique results in what is returned and no duplicates? For example, Elvis Presley has a track called Teddy Bear in my table 6 times but I only want to include it once? luuk34 wrote: > > On 21-07-2011 18:59, chiefmccrossan wrote: >> >> I have a Tracks table containing columns for artistname and trackname. I >> want >> to be able to select all artist names that have more than ten tracks. For > > SELECT artisname, count(tracks) > FROM Tracks > GROUP BY artistname > HAVING count(tracks)>10 > > > >> example Elvis Presley has more than 20 tracks in the Tracks table but >> Johnny >> Cash has only 5 tracks. I need to be able to count Elvis and discount >> Johnny >> cash? >> >> Any idea how i can do this? >> >> Thanks >> Adam > > > -- > Luuk > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32110258.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select names where occurences are greater than 10
On 7/21/2011 3:15 PM, chiefmccrossan wrote: > Perfect! Thanks a million! =) > > Just wondering, is there anyway to select only unique results in what is > returned and no duplicates? > > For example, Elvis Presley has a track called Teddy Bear in my table 6 times > but I only want to include it once? count(distinct track) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select names where occurences are greater than 10
Perfect! Thanks a million! =) Just wondering, is there anyway to select only unique results in what is returned and no duplicates? For example, Elvis Presley has a track called Teddy Bear in my table 6 times but I only want to include it once? luuk34 wrote: > > On 21-07-2011 18:59, chiefmccrossan wrote: >> >> I have a Tracks table containing columns for artistname and trackname. I >> want >> to be able to select all artist names that have more than ten tracks. For > > SELECT artisname, count(tracks) > FROM Tracks > GROUP BY artistname > HAVING count(tracks)>10 > > > >> example Elvis Presley has more than 20 tracks in the Tracks table but >> Johnny >> Cash has only 5 tracks. I need to be able to count Elvis and discount >> Johnny >> cash? >> >> Any idea how i can do this? >> >> Thanks >> Adam > > > -- > Luuk > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32110258.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select names where occurences are greater than 10
On 21-07-2011 18:59, chiefmccrossan wrote: > > I have a Tracks table containing columns for artistname and trackname. I want > to be able to select all artist names that have more than ten tracks. For SELECT artisname, count(tracks) FROM Tracks GROUP BY artistname HAVING count(tracks)>10 > example Elvis Presley has more than 20 tracks in the Tracks table but Johnny > Cash has only 5 tracks. I need to be able to count Elvis and discount Johnny > cash? > > Any idea how i can do this? > > Thanks > Adam -- Luuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
