Groupping together - SQL Server Forums

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)

Groupping together

Author

Topic

babloo
Starting Member

35 Posts

Posted - 2013-03-13 : 10:21:45

I have already asked this type of question before but can not generate expected results with your suggestions using old answer, need your assistance again please.

Previous thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183351

This is my Query:
select distinct case
when XML LIKE '%STATUS="NEW"%' then 'NEW Docs'
when XML LIKE '%STATUS="OLD"%' then 'OLD Docs'
end as GroupName,
SUM(Size) as Size1,
count(*) as Total
from contents
Where (XML LIKE ('%STATUS="NEW"%') OR (XML LIKE '%STATUS="OLD"%' ))
Group by xml,
case
when XML LIKE '%STATUS="NEW"%' then 'NEW Docs'
when XML LIKE '%STATUS="OLD"%' then 'OLD Docs'
end
order by SUM(Size), Total

Results right now:

NEW Docs 8402 1
NEW Docs 9715 1
NEW Docs 11112 1
OLD Docs 92948 1

Expected results:

NEW Docs 58458 3
OLD Docs 92948 1

Thank you.
Babloo

babloo
Starting Member

35 Posts

Posted - 2013-03-13 : 10:24:25

Sorry had to edit the URL, it was wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 10:27:24

[code]
select case
when XML LIKE '%STATUS="NEW"%' then 'NEW Docs'
when XML LIKE '%STATUS="OLD"%' then 'OLD Docs'
end as GroupName,
SUM(Size) as Size1,
count(*) as Total
from contents
Where (XML LIKE ('%STATUS="NEW"%') OR (XML LIKE '%STATUS="OLD"%' ))
Group by
case
when XML LIKE '%STATUS="NEW"%' then 'NEW Docs'
when XML LIKE '%STATUS="OLD"%' then 'OLD Docs'
end
order by Size1, Total
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

babloo
Starting Member

35 Posts

Posted - 2013-03-13 : 10:37:33

Thanks again Visakh appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-13 : 10:39:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources