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
[sqlite] Select names where occurences are greater than 10
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 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 -- View this message in context: http://old.nabble.com/Select-names-where-occurences-are-greater-than-10-tp32109108p32109108.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
