Count Having > 1

I couldn't get this right in SQL, so I tried Access GUI for help and didn't
get much farther.
In the end, I need to return a "1" or "0".
I need to Count the number of duped artists in DODSongs. If the total of any
one artist > 50% of the total artists Then "1" Else "0"
For example: Assume I had 20 songs. If any one artist is listed more than 10
times, I need to return "1"
SELECT DODSongs.ProjectID, COUNT(SELECT DODSongs.Artist FROM DODSongs
GROUP BY DODSongs.Artist HAVING Count(DODSongs.Artist) > 1) AS NumberArtists
FROM DODSongs RIGHT OUTER JOIN
DODProjects ON DODSongs.ProjectID = DODProjects.ID
WHERE (DODProjects.E = 'abc')
GROUP BY DODSongs.ProjectID
thanks!Look at this example
create table Candidate
(
candidateid int not null primary key,
[name] varchar(50) not null
)
create table Candidate_Skills
(
candidate_skills_id int not null primary key,
candidateid int not null references Candidate(candidateid),
skill varchar(20) not null
)
insert into Candidate values (1,'Chris')
insert into Candidate values (2,'Tom')
insert into Candidate values (3,'Mike')
insert into Candidate values (4,'Adam')
insert into Candidate_Skills values (1,1,'DB2')
insert into Candidate_Skills values (2,1,'Sybase')
insert into Candidate_Skills values (3,1,'Siebel')
insert into Candidate_Skills values (4,2,'SQL')
insert into Candidate_Skills values (5,3,'AIX')
insert into Candidate_Skills values (6,3,'Tivoli')
insert into Candidate_Skills values (7,4,'Linux')
insert into Candidate_Skills values (8,4,'VB')
Select Distinct C.CandidateID, D.Name
From
(Select A.CandidateID, A.Candidate_Skills_ID
From Candidate_Skills A
Join Candidate_Skills B
On A.CandidateID = B.CandidateID
Where A.Candidate_Skills_ID > B.Candidate_Skills_ID) AS C
Join Candidate D
On C.CandidateID = D.CandidateID
drop table Candidate_Skills
drop table Candidate
"shank" <shank@.tampabay.rr.com> wrote in message
news:Ovwz77F6FHA.1148@.tk2msftngp13.phx.gbl...
>I couldn't get this right in SQL, so I tried Access GUI for help and didn't
>get much farther.
> In the end, I need to return a "1" or "0".
> I need to Count the number of duped artists in DODSongs. If the total of
> any one artist > 50% of the total artists Then "1" Else "0"
> For example: Assume I had 20 songs. If any one artist is listed more than
> 10 times, I need to return "1"
> SELECT DODSongs.ProjectID, COUNT(SELECT DODSongs.Artist FROM DODSongs
> GROUP BY DODSongs.Artist HAVING Count(DODSongs.Artist) > 1) AS
> NumberArtists
> FROM DODSongs RIGHT OUTER JOIN
> DODProjects ON DODSongs.ProjectID = DODProjects.ID
> WHERE (DODProjects.E = 'abc')
> GROUP BY DODSongs.ProjectID
> thanks!
>|||shank (shank@.tampabay.rr.com) writes:
> I couldn't get this right in SQL, so I tried Access GUI for help and
> didn't get much farther.
> In the end, I need to return a "1" or "0".
> I need to Count the number of duped artists in DODSongs. If the total of
> any one artist > 50% of the total artists Then "1" Else "0"
> For example: Assume I had 20 songs. If any one artist is listed more
> than 10 times, I need to return "1"
> SELECT DODSongs.ProjectID, COUNT(SELECT DODSongs.Artist FROM
> DODSongs GROUP BY DODSongs.Artist HAVING Count(DODSongs.Artist) > 1) AS
> NumberArtists FROM DODSongs RIGHT OUTER JOIN
> DODProjects ON DODSongs.ProjectID = DODProjects.ID
> WHERE (DODProjects.E = 'abc') GROUP BY DODSongs.ProjectID
This might work:
SELECT ProjectID, Artist, CASE(CASE WHEN cnt >= 10 THEN 1 ELSE 0 END)
FROM (SELECT DS.ProjectID, DS.Artist, cnt = COUNT(*)
FROM DODSongs DS
RIGHT JOIN DODProjects DP ON DS.ProjectID = DP.ID
WHERE DP.E = 'abc'
GROUP BY DS.ProjectID , DS.Artist) AS
ORDER BY ProjectID, Artist
What you have in the parentheses is a derived table. Kind of a temp table
within the table, but only logically. SQL Server will compute the entire
query in what it estimates to be the most efficient way.
If this does not answer your question, please include:
o CREATE TABLE statements your tables.
o INSERT statement with sample data.
o The desired output given the sample.
This makes easy to develop a tested solution which solves your problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970DA5AB21E95Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> This might work:
> SELECT ProjectID, Artist, CASE(CASE WHEN cnt >= 10 THEN 1 ELSE 0 END)
> FROM (SELECT DS.ProjectID, DS.Artist, cnt = COUNT(*)
> FROM DODSongs DS
> RIGHT JOIN DODProjects DP ON DS.ProjectID = DP.ID
> WHERE DP.E = 'abc'
> GROUP BY DS.ProjectID , DS.Artist) AS
> ORDER BY ProjectID, Artist
> What you have in the parentheses is a derived table. Kind of a temp table
> within the table, but only logically. SQL Server will compute the entire
> query in what it estimates to be the most efficient way.
> If this does not answer your question, please include:
> o CREATE TABLE statements your tables.
> o INSERT statement with sample data.
> o The desired output given the sample.
> This makes easy to develop a tested solution which solves your problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
========================================
=======
Here's the 2 tables and test data trimmed to the basic needs.
To recap, the process should count the total records per [ProjectID] group,
then count the duped artists per [ProjectID] group. Duped artists cannot
represent more than 50% of any one group. When done [ProjectID] 9 should
return a 1 because there's (12) Artist12 in a group of 23 records.
[ProjectID] 10 should return a 0 because there's (12) records in that group
and Artist1 is there 6 times.
Thanks for your help!
CREATE TABLE [DODProjects] (
[Email] [varchar] (100),
[ID] [numeric](18, 0)
) ON [PRIMARY]
GO
CREATE TABLE [DODSongs] (
[ProjectID] [numeric](18, 0),
[Artist] [varchar] (255),
) ON [PRIMARY]
GO
insert into DODProjects values ('abc',9)
insert into DODProjects values ('abc',10)
insert into DODSongs values (9,'Artist1')
insert into DODSongs values (9,'Artist2')
insert into DODSongs values (9,'Artist3')
insert into DODSongs values (9,'Artist4')
insert into DODSongs values (9,'Artist5')
insert into DODSongs values (9,'Artist6')
insert into DODSongs values (9,'Artist7')
insert into DODSongs values (9,'Artist8')
insert into DODSongs values (9,'Artist9')
insert into DODSongs values (9,'Artist10')
insert into DODSongs values (9,'Artist11')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (9,'Artist12')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist1')
insert into DODSongs values (10,'Artist2')
insert into DODSongs values (10,'Artist3')
insert into DODSongs values (10,'Artist4')
insert into DODSongs values (10,'Artist5')
insert into DODSongs values (10,'Artist6')
insert into DODSongs values (10,'Artist7')
========================================
===|||shank (shank@.tampabay.rr.com) writes:
> Here's the 2 tables and test data trimmed to the basic needs.
> To recap, the process should count the total records per [ProjectID]
> group, then count the duped artists per [ProjectID] group. Duped artists
> cannot represent more than 50% of any one group. When done [ProjectID] 9
> should return a 1 because there's (12) Artist12 in a group of 23
> records. [ProjectID] 10 should return a 0 because there's (12) records
> in that group and Artist1 is there 6 times.
Thanks for the test data! That made it a little easier. Does this
query meet your needs:
SELECT ProjectID, MAX(CASE WHEN cnt >= 10 THEN 1 ELSE 0 END)
FROM (SELECT DS.ProjectID, DS.Artist, cnt = COUNT(*)
FROM DODSongs DS
RIGHT JOIN DODProjects DP ON DS.ProjectID = DP.ID
WHERE DP.Email = 'abc'
GROUP BY DS.ProjectID , DS.Artist) AS z
GROUP BY ProjectID
ORDER BY ProjectID
The inner query counds artists per project, and the outer query determines
1 or 0 for the project.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns970E5B9E64C2Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> Thanks for the test data! That made it a little easier. Does this
> query meet your needs:
> SELECT ProjectID, MAX(CASE WHEN cnt >= 10 THEN 1 ELSE 0 END)
> FROM (SELECT DS.ProjectID, DS.Artist, cnt = COUNT(*)
> FROM DODSongs DS
> RIGHT JOIN DODProjects DP ON DS.ProjectID = DP.ID
> WHERE DP.Email = 'abc'
> GROUP BY DS.ProjectID , DS.Artist) AS z
> GROUP BY ProjectID
> ORDER BY ProjectID
> The inner query counds artists per project, and the outer query determines
> 1 or 0 for the project.
================================
I appreciate your help, but you are assuming that there will always be 20
songs for a total count. Actually, there can be any number for the total of
songs. There may be 2, 5, 20, or 30 or whatever. I need to count that total
then make sure there's no more than 50% of any one artist.
thanks again!!!|||On Sun, 13 Nov 2005 20:05:25 -0500, shank wrote:

>I appreciate your help, but you are assuming that there will always be 20
>songs for a total count. Actually, there can be any number for the total of
>songs. There may be 2, 5, 20, or 30 or whatever. I need to count that total
>then make sure there's no more than 50% of any one artist.
Hi shank,
Try if this wsuits your needs:
SELECT ProjectID,
CASE WHEN MAX(Cnt) * 2 > (SELECT COUNT(*)
FROM DODSongs
WHERE ProjectID = a.ProjectID)
THEN 1 ELSE 0 END
FROM (SELECT ProjectID, Artist, COUNT(*) AS Cnt
FROM DODSongs
GROUP BY ProjectID, Artist) AS a
GROUP BY ProjectID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||shank (shank@.tampabay.rr.com) writes:
> I appreciate your help, but you are assuming that there will always be
> 20 songs for a total count. Actually, there can be any number for the
> total of songs. There may be 2, 5, 20, or 30 or whatever. I need to
> count that total then make sure there's no more than 50% of any one
> artist.
OK, this one is a little obscure, but more effective that Hugo's query:
SELECT ProjectID,
CASE WHEN 2 * MAX(artistcnt) > SUM(artistcnt) THEN 1 ELSE 0 END
FROM (SELECT DS.ProjectID, artistcnt = COUNT(*)
FROM DODSongs DS
RIGHT JOIN DODProjects DP ON DS.ProjectID = DP.ID
WHERE DP.Email = 'abc'
GROUP BY DS.ProjectID, DS.Artist) AS z
GROUP BY ProjectID
ORDER BY ProjectID
SUM(artistcnt) is in fact the total number of entries for the project.
I am using 2* on the MAX side, rather than / 2 on the count side, to
avoid issues with integer division.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 14 Nov 2005 23:10:31 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>SUM(artistcnt) is in fact the total number of entries for the project.
Neat!
Thanks, Erland!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This appears to be great so far!
Thanks very much to both of you!!!