Select Multi Row, to be Singel Row
-
Hi All, Any idea to select this data ?
NGB CCU 0 0 0 0 6
NGB CCU 0 0 4 0 0
SHA SIN 0 0 0 0 4
SHA SIN 0 0 0 1 0
SHA SIN 0 0 25 0 0To be result as below
NGB CCU 0 0 4 0 6
SHA SIN 0 0 25 1 4Thanks and best Regards.
Its a simple sum() and Group By query
Select col1, col2, sum(col3), sum(col4) etc
Group By col1,col2Never underestimate the power of human stupidity RAH
-
Its a simple sum() and Group By query
Select col1, col2, sum(col3), sum(col4) etc
Group By col1,col2Never underestimate the power of human stupidity RAH
Oh! yes such a simple case :) I didn't get that idea. Thank you. But, Any Idea again? To get this data I had to use the following query, the query is already complicated. Is there any way to Sum up the data as you suggested without dumping this data into temp Table?
NGB CCU 0 0 0 0 0 0 0 0 6
NGB CCU 0 0 0 0 0 0 4 0 0
SHA SIN 0 0 0 0 0 0 0 0 4
SHA SIN 0 0 0 0 0 0 0 1 0
SHA SIN 0 0 0 0 0 0 25 0 0-- Get Refeer, Open Top/Flatrack and Normal
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
(case when container_code like '%R%20' Then qty else '' end ) as 'R_D20',
0 as 'R_D40',
(case when container_code like '%HR%40' Then qty else '' end ) as 'R_H40',
(case when container_code ='FL20' OR container_code ='OT20' Then qty else '' end ) as 'OT_D20',
(case when container_code ='FL40' OR container_code ='OT40' Then qty else '' end ) as 'OT_D40',
0 as 'OT_H40',
(case when container_code='D20' Then qty else '' end ) as 'N_D20',
(case when container_code='D40' Then qty else '' end ) as 'N_D40',
(case when container_code='HC40' Then qty else '' end ) as 'N_H40'
From myTable
order by POL,POD -
Oh! yes such a simple case :) I didn't get that idea. Thank you. But, Any Idea again? To get this data I had to use the following query, the query is already complicated. Is there any way to Sum up the data as you suggested without dumping this data into temp Table?
NGB CCU 0 0 0 0 0 0 0 0 6
NGB CCU 0 0 0 0 0 0 4 0 0
SHA SIN 0 0 0 0 0 0 0 0 4
SHA SIN 0 0 0 0 0 0 0 1 0
SHA SIN 0 0 0 0 0 0 25 0 0-- Get Refeer, Open Top/Flatrack and Normal
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
(case when container_code like '%R%20' Then qty else '' end ) as 'R_D20',
0 as 'R_D40',
(case when container_code like '%HR%40' Then qty else '' end ) as 'R_H40',
(case when container_code ='FL20' OR container_code ='OT20' Then qty else '' end ) as 'OT_D20',
(case when container_code ='FL40' OR container_code ='OT40' Then qty else '' end ) as 'OT_D40',
0 as 'OT_H40',
(case when container_code='D20' Then qty else '' end ) as 'N_D20',
(case when container_code='D40' Then qty else '' end ) as 'N_D40',
(case when container_code='HC40' Then qty else '' end ) as 'N_H40'
From myTable
order by POL,PODTry this:
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
Sum(case when container_code like '%R%20' Then qty else 0 end ) as 'R_D20',
0 as 'R_D40',
Sum(case when container_code like '%HR%40' Then qty else 0 end ) as 'R_H40',
Sum(case when container_code ='FL20' OR container_code ='OT20' Then qty else 0 end ) as 'OT_D20',
Sum(case when container_code ='FL40' OR container_code ='OT40' Then qty else 0 end ) as 'OT_D40',
0 as 'OT_H40',
Sum(case when container_code='D20' Then qty else 0 end ) as 'N_D20',
Sum(case when container_code='D40' Then qty else 0 end ) as 'N_D40',
Sum(case when container_code='HC40' Then qty else 0 end ) as 'N_H40'
From myTable
Group by POL,POD
order by POL,PODNote that I changed your empty strings to 0 to avoid mixing of types.
-
Try this:
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
Sum(case when container_code like '%R%20' Then qty else 0 end ) as 'R_D20',
0 as 'R_D40',
Sum(case when container_code like '%HR%40' Then qty else 0 end ) as 'R_H40',
Sum(case when container_code ='FL20' OR container_code ='OT20' Then qty else 0 end ) as 'OT_D20',
Sum(case when container_code ='FL40' OR container_code ='OT40' Then qty else 0 end ) as 'OT_D40',
0 as 'OT_H40',
Sum(case when container_code='D20' Then qty else 0 end ) as 'N_D20',
Sum(case when container_code='D40' Then qty else 0 end ) as 'N_D40',
Sum(case when container_code='HC40' Then qty else 0 end ) as 'N_H40'
From myTable
Group by POL,POD
order by POL,PODNote that I changed your empty strings to 0 to avoid mixing of types.
Hi thank you for the reply. When I use Group By POL,POD as your suggested Get error for "Container_Code and Qty is invalid in the select list because it is not contained in either an aggregate function or the Group By clause." When I add these two in Group by Clause
Group by POL,POD,container_code,qty
Got the result as same as without using Group By.
-
Hi All, Any idea to select this data ?
NGB CCU 0 0 0 0 6
NGB CCU 0 0 4 0 0
SHA SIN 0 0 0 0 4
SHA SIN 0 0 0 1 0
SHA SIN 0 0 25 0 0To be result as below
NGB CCU 0 0 4 0 6
SHA SIN 0 0 25 1 4Thanks and best Regards.
Dear You can use group by clause. select c1, c2, isnull(sum(c3),0), isnull(sum(c4),0) from tbl group by c1, c2 use having clause for again filter your output. use it after grope by clause.
-
Dear You can use group by clause. select c1, c2, isnull(sum(c3),0), isnull(sum(c4),0) from tbl group by c1, c2 use having clause for again filter your output. use it after grope by clause.