18
Views
3
Comments
Solved
combile two row in single Using SQL

Hi All,

I am using Advance sql for getting count and i want combine this (StatusID=3) two row in single row and sum of both row .

How can i achieved this -

SELECT count(1) as TotalCount  ,'' as Blank,
case 
when {AccountDetails}.[AccountStatusID] is null
then
3
else
{AccountDetails}.[AccountStatusID]
end as StatusID
from {AllAccountSubCate} 
Left Join {AccountDetails} on {AccountDetails}.[AccountID]={AllAccountSubCate}.[AccountID] and {AccountDetails}.[AccountSubCategoryID]={AllAccountSubCate}.[AccountSubCategoryID]
Where 
{AccountDetails}.[AccountStatusID]<>2 or {AccountDetails}.[AccountStatusID] is null
and 
((CASE WHEN (@AccountID = 0) THEN 1 ELSE (CASE WHEN ((({AllAccountSubCate}.[AccountID])) = ((@AccountID))) THEN 1 ELSE 0 END) END) = 1)
group by {AccountDetails}.[AccountStatusID]


Thanks in Advance.

Piya

Rank: #89
Solution

Hi Piya,

You can use select query on this query just like

SELECT sum(TotalCount),Blank, StatusID from
( 
SELECT count(1) as TotalCount  ,'' as Blank,
case 
when {AccountDetails}.[AccountStatusID] is null
then
3
else
{AccountDetails}.[AccountStatusID]
end as StatusID
from {AllAccountSubCate} 
Left Join {AccountDetails} on {AccountDetails}.[AccountID]={AllAccountSubCate}.[AccountID] and {AccountDetails}.[AccountSubCategoryID]={AllAccountSubCate}.[AccountSubCategoryID]
Where 
{AccountDetails}.[AccountStatusID]<>2 or {AccountDetails}.[AccountStatusID] is null
and 
((CASE WHEN (@AccountID = 0) THEN 1 ELSE (CASE WHEN ((({AllAccountSubCate}.[AccountID])) = ((@AccountID))) THEN 1 ELSE 0 END) END) = 1)
group by {AccountDetails}.[AccountStatusID]
) As ALlCount
Group by StatusID,Blank

and your problem will resolved.


Hope this will help you

Regards

Rahul

Rank: #70

Hi Piya,


You can do it like this:

SELECT count(1) as TotalCount ,'' as Blank,
SUM(case
when {AccountDetails}.[AccountStatusID] = 3 THEN 1 ELSE 0 END) CountStatus3
from {AllAccountSubCate}
Left Join {AccountDetails} on {AccountDetails}.[AccountID]={AllAccountSubCate}.[AccountID] and {AccountDetails}.[AccountSubCategoryID]={AllAccountSubCate}.[AccountSubCategoryID]
Where
{AccountDetails}.[AccountStatusID]<>2 or {AccountDetails}.[AccountStatusID] is null
and
((CASE WHEN (@AccountID = 0) THEN 1 ELSE (CASE WHEN ((({AllAccountSubCate}.[AccountID])) = ((@AccountID))) THEN 1 ELSE 0 END) END) = 1)


The rational behind it is you want to sum 1 if status is 3, and 0 if the status is not 3.


Hope it helps.


Cheers,

João

Rank: #89
Solution

Hi Piya,

You can use select query on this query just like

SELECT sum(TotalCount),Blank, StatusID from
( 
SELECT count(1) as TotalCount  ,'' as Blank,
case 
when {AccountDetails}.[AccountStatusID] is null
then
3
else
{AccountDetails}.[AccountStatusID]
end as StatusID
from {AllAccountSubCate} 
Left Join {AccountDetails} on {AccountDetails}.[AccountID]={AllAccountSubCate}.[AccountID] and {AccountDetails}.[AccountSubCategoryID]={AllAccountSubCate}.[AccountSubCategoryID]
Where 
{AccountDetails}.[AccountStatusID]<>2 or {AccountDetails}.[AccountStatusID] is null
and 
((CASE WHEN (@AccountID = 0) THEN 1 ELSE (CASE WHEN ((({AllAccountSubCate}.[AccountID])) = ((@AccountID))) THEN 1 ELSE 0 END) END) = 1)
group by {AccountDetails}.[AccountStatusID]
) As ALlCount
Group by StatusID,Blank

and your problem will resolved.


Hope this will help you

Regards

Rahul

Rank: #27554

Thanks @Rahul,

I want exactly this.


Piya