18
Views
3
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]```

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```

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```