CODEWITHSUNDEEP
sqlsql-servergroup-byhavinghaving-clause
DEwok
DEwok

Reputation: 73

SQL Group By and Having display fields

I’m trying to pull the people records associated with 405B folders where there is more than 1 folderpeople record. I’ve used the following SQL to get the counts, but what I can’t figure out is how to pull the actual people information ,p.namefirst, p.namelast, p.organizationname, and fp.peoplecode. I’ve tried using various permutations on GROUP BY and HAVING, but I keep getting errors. I only want the data from folder, folder people and people where there is more than 1 folderpeople record.

SELECT f.folderrsn, Count(fp.folderrsn)
FROM folder f
INNER JOIN folderpeople fp ON f.folderrsn = fp.folderrsn
WHERE f.foldertype = '405B'
GROUP BY f.folderrsn
HAVING Count(fp.folderrsn) > 1
ORDER BY f.folderrsn

any suggestions?

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Presumably, you have some sort of "persons" table that has columns that you reference in your question. And this can be joined back to folderpeople.

Given that, you could solve the problem by joining the information back in. That's a bit message, because you can also do this using window functions in SQL Server:

SELECT pf.*
FROM (SELECT p.*, f.folderrsn,
             count(*) over (partition by f.folderrsn) as cnt
      FROM folder f INNER JOIN
           folderpeople fp
           ON f.folderrsn = fp.folderrsn JOIN
           people p
           ON fp.personid = p.personid
      WHERE f.foldertype = '405B'
     ) pf
WHERE cnt > 1
ORDER BY olderrsn;

Upvotes: 0

pmbAustin
pmbAustin

Reputation: 3970

Not really understanding your schema or data (examples would really help here), it SOUNDS like you want something like this?

SELECT p.namefirst, p.namelast, p.organizationname, fp.peoplecode
  FROM folder f
       INNER JOIN folderpeople fp ON f.folderrsn = fp.folderrsn
WHERE fp.folderrsn IN (
    SELECT folderrsn
      FROM folder 
     WHERE folderType = '405B'
     GROUP BY folderrsn
    HAVING COUNT(folderrsn) > 1
)

Basically, select all the data you want from the tables you want, and then filter the results to only those where the folder matches your criteria. So I may have the details wrong here, but this might give you the format you're looking for in how to combine a GROUP BY/HAVING with getting all the data from tables and columns you're not grouping on.

Upvotes: 1

Related Questions