September 12, 2011 at 1:48 pm
So I have this statement:
SELECT ClassDefinition.symbolic_name, COUNT(DocVersion.object_class_id)
FROM ClassDefinition INNER JOIN
DocVersion ON ClassDefinition.object_id = DocVersion.object_class_id
where DocVersion.version_status = '1'
group by ClassDefinition.symbolic_name, DocVersion.object_class_id
order by ClassDefinition.symbolic_name
It gives me a list of names and counts. What I need is to be able to identify 2 ClassDefinition.symbolic_name and add up the COUNT for both names together.
For instance:
AirPersonnelRecord, 2911
HQANGDocument, 1
End Result:
AirAngCombine, 2912
Any Ideas?
September 12, 2011 at 2:05 pm
This doesn't seem clean to me but it worked...
SELECT (SELECT COUNT(DocVersion.object_class_id)
FROM ClassDefinition
INNER JOIN DocVersion ON ClassDefinition.object_id = DocVersion.object_class_id
where DocVersion.version_status = '1' AND ClassDefinition.symbolic_name = 'HQANGDocument'
group by ClassDefinition.symbolic_name, DocVersion.object_class_id) +
(SELECT COUNT(DocVersion.object_class_id)
FROM ClassDefinition
INNER JOIN DocVersion ON ClassDefinition.object_id = DocVersion.object_class_id
where DocVersion.version_status = '1' AND ClassDefinition.symbolic_name = 'AirPersonnelRecord'
group by ClassDefinition.symbolic_name, DocVersion.object_class_id)
September 18, 2011 at 6:20 am
SELECT ds.symbolic_name,
SUM(ds.count_object_class_id)
FROM (SELECT CASE
WHEN cd.symbolic_name = 'AirPersonnelRecord'
OR cd.symbolic_name = 'HQANGDocument' THEN
'AirAngCombine'
ELSE cd.symbolic_name
END AS symbolic_name,
COUNT(dv.object_class_id) AS count_object_class_id
FROM classdefinition AS cd
INNER JOIN docversion AS dv
ON cd.object_id = dv.object_class_id
WHERE dv.version_status = '1'
GROUP BY cd.symbolic_name,
dv.object_class_id) AS ds
GROUP BY ds.symbolic_name
ORDER BY ds.symbolic_name
rename the two original values you want to combine to the new-combined-value in a subquery, then just sum everything up with a group by.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply