select convert(Date, char(10)) as TransCreateDate,
ContentProviderName as CompanyName,
sum(TotalCount) as TotalRequest,
sum(CASE WHEN status= 'F' THEN TotalCount ELSE '0' END) as SuccessfullyDownload,
sum(CASE WHEN Paid= 'Y' THEN TotalCount ELSE '0' END) as SuccessCharged,
price as Price,
sum(CASE WHEN Paid = 'Y' THEN GrossRevenue ELSE 0 END) as GrossRevenue,
ItemType as ContentType,
sum(WEBChannel) as WEBChannel,
sum(WAPChannel) as WAPChannel,
sum(SMSChannel) as SMSChannel,
sum(UMBChannel) as UMBChannel,
sum(VASChannel) as VASChannel
#,status,Paid
from AortaReporting
where convert(Date, char(10)) <= '2013-05-01' and convert(Date, char(10)) >= '2013-05-01' and ItemType = "FTD" and ContentProviderID = 162
GROUP BY
TransCreateDate,ContentProviderID,price
select
ProductName as ContentName,Album,Artist,ContentProviderName as CompanyName,
sum(CASE WHEN Paid= 'Y' THEN TotalCount ELSE '0' END) as SuccessCharged,
ItemType as ContentType,Price as Price,
sum(WEBChannel) as WEBChannel,sum(WAPChannel) as WAPChannel,sum(SMSChannel) as SMSChannel,sum(UMBChannel) as UMBChannel,sum(VASChannel) as VASChannel
,status,Paid
from AortaReporting where convert(Date, char(10)) <= '2013-05-01' and convert(Date, char(10)) >= '2013-05-01' and ItemType = 'FTD' and ContentProviderID = 162
GROUP BY ProductName ,Album,Artist,ContentProviderID