CODEWITHSUNDEEP
sqlpostgresql
user6652930
user6652930

Reputation:

Select Where Same Count

How do i count the number of papers with equal number of authors?

I wanna return number of authors, number of papers, sorted ascending by number of authors.

Here is my SQL:

CREATE TABLE Papers(
  PKey INT PRIMARY KEY,
  Title VARCHAR(512) NOT NULL,
  Pages VARCHAR(64),
  CKey INT REFERENCES Conferences,
  JKey INT REFERENCES Journals,
  CHECK((CKey IS NOT NULL AND JKey IS NULL)
    OR (CKey IS NULL AND JKey IS NOT NULL))
);

CREATE TABLE AuthPapers(
  PKey INT REFERENCES Papers,
  AKey INT REFERENCES Persons,
  Rank INT NOT NULL,
  PRIMARY KEY(PKey, AKey),
  CHECK(Rank >= 1)
);

CREATE TABLE Persons(
  AKey INT PRIMARY KEY,
  Name VARCHAR(128) UNIQUE NOT NULL,
  Website VARCHAR(256),
  IKey INT REFERENCES Institutions
);

How do I achieve this?

Result

Result of SQL

Upvotes: 0

Views: 30

Answers (1)

Gabriel Durac
Gabriel Durac

Reputation: 2760

select 
  AuthCount,
  COUNT(Pkey) as PaperCount 
FROM (
      select 
      PKey, 
      COUNT(AKey) as AuthCount 
      FROM AuthPapers 
      GROUP BY PKey) as t 
GROUP BY AuthCount 
ORDER BY AuthCount

Upvotes: 1

Related Questions