Including a column to display without GROUPING it

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 2008 Forums

Transact-SQL (2008)

Including a column to display without GROUPING it

Author

Topic

Barr26
Starting Member

2 Posts

Posted - 2013-07-23 : 11:38:30

I've been fighting this one for a while, and would love some help. I'm sure there's a simple answer. Basically, I have two tables whose data I need to group together. STATES is simply a list of states with STATEID and STATENAME columns, and DOCSTATES contains the STATEID and DOCUMENTID column. I need to group these together to identify which STATEID values exist in DOCSTATES so I can provide a checked checkbox on my page. The query:

SELECT X.stateid, COUNT(stateid) AS statecount
FROM (SELECT stateid, statename
FROM docstates WHERE documentid = '43'
UNION ALL
SELECT stateid, statename
FROM states)
AS X(stateid, statename)
GROUP BY stateid

This works just fine. I get a list of all of the STATEIDs, and the matching STATECOUNT values are all '2' instead of '1'. So in my ASP code, I'm set. The only thing missing is the STATENAME, but whenever I try to include it in the top query, I get that annoying message about having to include it in the GROUP BY clause. When I add STATENAME to the GROUP BY clause, it "de-aggregates" the COUNT column, and I lose the '2' values.

I hope that makes sense. I feel like I'm really close, just can't seem to fit that STATENAME column in.

Thanks!

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-23 : 12:01:37

Try this
[CODE]

SELECT X.stateid, MAX(statename), COUNT(stateid) AS statecount
FROM (SELECT stateid, statename
FROM docstates WHERE documentid = '43'
UNION ALL
SELECT stateid, statename
FROM states)
AS X(stateid, statename)
GROUP BY stateid

[/CODE]

Barr26
Starting Member

2 Posts

Posted - 2013-07-23 : 12:15:57

That worked, thank you so much! Makes perfect sense.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 12:33:32

[code]SELECT s.StateID,
COUNT(ds.StateID) AS StateCount
FROM dbo.States AS s
LEFT JOIN dbo.DocStated AS ds ON ds.StateID = s.StateID
AND ds.DocumentID = '43'
GROUP BY s.StateID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-23 : 13:12:49

[code]SELECT s.StateID,
ISNULL(ds.StateCount, 0) AS StateCount
FROM dbo.States AS s
LEFT JOIN (
SELECT StateID,
COUNT(*) AS StateCount
FROM dbo.DocStates
WHERE DocumentID = '43'
GROUP BY StateID
) AS ds ON ds.StateID = s.StateID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources