SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Groupping together		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

babloo
Starting Member

USA
31 Posts
Posted - 03/13/2013 :  10:21:45  Show Profile  Reply with Quote
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
Edited by - babloo on 03/13/2013 10:24:08

babloo
Starting Member

USA
31 Posts
Posted - 03/13/2013 :  10:24:25  Show Profile  Reply with Quote
Sorry had to edit the URL, it was wrong.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 03/13/2013 :  10:27:24  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

babloo
Starting Member

USA
31 Posts
Posted - 03/13/2013 :  10:37:33  Show Profile  Reply with Quote
Thanks again Visakh appreciate your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 03/13/2013 :  10:39:09  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000