StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
15725175
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2013-03-31T00:46:16.133
FavoriteCount
0
LastActivityDate
2013-03-31T00:58:49.730
LastEditDate
2013-03-31T00:58:49.730
LastEditorUserId
1144035
OwnerUserId
1144035
ParentId
15724785
PostTypeId
2
Score
1
ViewCount
0
LastEditorDisplayName
text
Body
First, we need to find out if the "colors" for two "ids" are the same. The following query does a full outer join on <code>color</code>, and then aggregates by <code>id</code>. If two <code>id</code>s have the same color, then the full outer join always matches -- there are no NULLs: <pre><code>select s1.id, s2.id from s s1 full outer join s s2 on s1.color = s2.color group by s1.id, s2.id having sum(case when s1.color is null then 1 else 0 end) = 0 and sum(case when s2.color is null then 1 else 0 end) = 0 </code></pre> Using the same idea, we can assign the minimum s1.id as the "id" of the group. This group id then gives us the information needed for the final aggregation: <pre><code>select s3.groupid, sum(s3.quantity) as quantity, s3.color as color from (select min(s1.id) as groupid, s2.id from (select s1.id, s2.id from s s1 full outer join s s2 on s1.color = s2.color group by s1.id, s2.id having sum(case when s1.color is null then 1 else 0 end) = 0 and sum(case when s2.color is null then 1 else 0 end) = 0 ) ss group by s2.id ) sg join s s3 on sg.id = s.id group by sg.groupid, s3.color </code></pre> In DB2 you can also do this with <code>listagg</code>. First you need to get the list of colors to identify commonality, and then use it: <pre><code>select min(s.id) as groupid, sum(s.quantity) as quantity, s.color from (select id, listagg(color order by color) as thegroup from s group by id ) sg join s on sg.id = s.id group by sg.thegroup, s.color </code></pre> This is probably more efficient than the first solution.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POComplex multi row aggregate SQL query
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USGordon Linoff
UserOwnerUserId
1. USGordon Linoff
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POComplex multi row aggregate SQL query
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COthank you! Brilliant use of the full outer join and also good call on the LISTAGG function!
 singulars
 PostPostId
 PO
 UserUserId
 USuser1527312

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.