Adding an Ungrouped Column to Groups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding an Ungrouped Column to Groups

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-16 : 06:09:03

Once I have a functional statement grouping nicely, how do I just add another column(s) in there afterwards, using the objects and joins, not included in the grouping? Do I seperate the group work in parenthesis, or what? I really hate this point in SQL, and it's where I quit and use Crystal Reports but I can't today. How do I step back and add an ungrouped column?

In fact, what I need to do is add the de.PatientNumber as a column to the select list - part of the joins... but don't want to group on that!

SELECT
vwKC60.Code AS BaseCode,
vwKC60.Description,
SUM(patindex('M',de.Sex)) AS TotalMale,
SUM(PATINDEX(@sSexOrnBiSex + 'F',di.Acquired + de.Sex)) + SUM(PATINDEX(@sSexOrnHoSex + 'M', di.Acquired + de.Sex)) AS HoBiMaleOnly,
SUM(patindex('F',de.Sex)) AS TotalFemale
FROM vwSHKC60BaseCode vwKC60
LEFT JOIN Diagnosis di ON vwKC60.Code = di.Diagnosis
LEFT JOIN Demographic de ON di.PatientNumber = de.PatientNumber
LEFT JOIN Episode ep ON ep.ID = di.ParentID
LEFT JOIN Locations AS lo ON ep.Location = lo.Coded
WHERE (di.DiagnosisDate BETWEEN '01-06-2009' AND '31-08-2009')
AND (di.Provisional = 0)
AND (di.RecordDeleted = 0)
AND ep.Location = 'GU'
AND ep.Location = 'GU'
OR (lo.BelongsTo LIKE '%GU%')
GROUP BY
ALL vwKC60.Code, vwKC60.Description
ORDER BY
vwKC60.Code

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:43:03

If there are many different PatientNumbers in the grouped data you cannot display them all because in a group all values have to be equal if they are not in aggregate functions.

See this example:


AcctNo Name      payed
1      Miller    5
2      Miller    1
3      Smith     4
4      Smith     3

Now you can group by Name and sum(payed)
result would be
Miller 6
Smith 7

So there is ONE line per grouping result!
In this ONE line you cannot display AcctNo 1 and 2 or 3 and 4
The only thing you can do is min(AcctNo) or max(AcctNo) to get only ONE value per group.

No, you're never too old to Yak'n'Roll if you're too young to die.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-16 : 07:20:27

The grouping is not a problem. It's doing exactly what I want.

I want to add a column to the select list, without grouping on it, which I think requires a setup something like this, but I cannot get the syntax right...

select PatientNumber
from Demographic
inner join
(SELECT
vwKC60.Code AS BaseCode,
vwKC60.Description,
SUM(patindex('M',de.Sex)) AS TotalMale,
SUM(PATINDEX(@sSexOrnBiSex + 'F',di.Acquired + de.Sex)) + SUM(PATINDEX(@sSexOrnHoSex + 'M', di.Acquired + de.Sex)) AS HoBiMaleOnly,
SUM(patindex('F',de.Sex)) AS TotalFemale
FROM vwSHKC60BaseCode vwKC60
LEFT JOIN Diagnosis di ON vwKC60.Code = di.Diagnosis
LEFT JOIN Demographic de ON di.PatientNumber = de.PatientNumber
LEFT JOIN Episode ep ON ep.ID = di.ParentID
LEFT JOIN Locations AS lo ON ep.Location = lo.Coded
WHERE (di.DiagnosisDate BETWEEN '01-06-2009' AND '31-08-2009')
AND (di.Provisional = 0)
AND (di.RecordDeleted = 0)
AND ep.Location = 'GU'
AND ep.Location = 'GU'
OR (lo.BelongsTo LIKE '%GU%')
GROUP BY
ALL vwKC60.Code, vwKC60.Description) as X on Demographic.PatientNumber = X.PatientNumber
ORDER BY
vwKC60.Code

but I think the problem is that PatientNumber was not selected in the first case anyway, to make the link to...

Is maybe creating a derived table of the original grouping statement, and then attach extra columns to that? Sounds simple

Please?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 07:47:09

Your derived table is grouping many records into one record.
I think there are many different PatientNumbers within the underlaying records.
Hence it is not possible to have one PatientNumber per grouped record.

Maybe you want to retrieve all PatientNumbers which are matching the WHERE clause of your derived table.
So you have to do the select without grouping and aggregating.

No, you're never too old to Yak'n'Roll if you're too young to die.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-16 : 08:16:47

Very apt advice. Need to check with boss what's wanted. The statement is totalling summaries. Cannot show PatientNumber for even two cases. Review

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources