CODEWITHSUNDEEP
sql
aceminer
aceminer

Reputation: 4295

Taking variables outside of query in sql

I know my title is a bit misleading but i am unsure of what would be a good title.

Authored has 2 columns namely ID, PubID

Is there anyway i could output the P into my result.

I would like to know for each respective ID, PubID pair, how many rows would have the same PubID but different ID.

select a.authorId, P 
from Authored A 
WHERE 1 < 

(Select count(*) as P
from Authored B
where A.pubId = B.pubId
AND A.authorId<> B.authorId)

Thanks to all who have answered.

Table

AuthorID       pubID
1                2
3                2
4                2
10               1
11               1

Expected Result

AuthorID     NumberOfOccurenceOfDiffAuthIDWithSamePubID
1                       3
3                       3
4                       3
10                      2
11                      2

Upvotes: 0

Views: 57

Answers (3)

Kaf
Kaf

Reputation: 33809

Updated using count() over():

Fiddle demo:

select a.AuthorId, count(*) over(partition by pubId) counts
from Authored a 
order by a.AuthorId;

Upvotes: 1

Jodrell
Jodrell

Reputation: 35716

Is this what you mean, its not clear what you are asking? Fiddle here.

select
    count(ID) P,
    PubID
from
    (select distinct ID, pubID from Authored) d
group by
    PubID

This will give you the number of distinct `ID/authorId` for each `PubID\pubId`

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try below query for MS Sql server:

select 
    *, 
    COUNT(*) over (partition by pubId) 
From Authored  
where authorId<>pubId

Upvotes: 0

Related Questions