COUNT WITH A GROUP BY 2

[PrgrmsAll]

Hello:
I have a table listing Project_ID, Project_Name, Project_Type and State.
For example (sorry if it loses alignment):

Project_ID Project_Name Project_Type State
01 Pillows Bedroom UT
02 TableCloth Kitchen HI
03 Cushion LivingRoom TX
04 Lantern Outdoor HI
05 Curtains Bedroom HI
06 SinkCover Kitchen HI
07 Mat LivingRoom UT
08 Pots Outdoor TX
...


I need to create output that will display a count of each Project_Type for each State.
For example (sorry if it loses alignment):

State Bedroom Kitchen LivingRoom Outdoor
===============================================================
UT 1 0 1 0
TX 0 0 1 1
HI 1 2 0 1


Can any one show me the way to write this? I believe my issue is probably in the GROUP BY.

Any advice is appreciated.

Thanks in advance!

 

[mercwrought]

you can try something like this

select state,sum(bedroom),sum(kitchen),sum(livingroom),sum(outdoor)
from 
(
select State as state,count(*) as bedroom ,0 as kitchen, 0 as livingroom, 0 as outdoor
from table where Project_Name = 'Bedroom'
group by state

union all

select State as state,0 as bedroom ,count(*) as kitchen, 0 as livingroom, 0 as outdoor
from table where Project_Name = 'kitchen'
group by state

union all

select State as state,0 as bedroom ,0 as kitchen, count(*) as livingroom, 0 as outdoor
from table where Project_Name = 'livingroom'
group by state

union all

select State as state,0 as bedroom ,0 as kitchen, 0 as livingroom, count(*) as outdoor
from table where Project_Name = 'outdoor'
group by state
) as XX
group by state

 

[PrgrmsAll]

I seem to be getting this message:

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a varchar data type as an argument.

 

[mercwrought]

oops I see atleast one error I posted. Can you post your table script?

 

[PrgrmsAll]

I probably over-simplified the example but here is the script:

===

select TS_PLAN, sum('Assigned or IS Testing'),sum('Prod Review'),sum('UAT or Approval'),sum('Closed'), sum('Other'),sum('Unassigned')
from
(
select TS_PLAN as TS_PLAN,count(*) as 'Assigned or IS Testing', 0 as 'Prod Review', 0 as 'UAT or Approval', 0 as 'Closed', 0 as 'Other', 0 as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'Assigned or IS Testing'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as 'Assigned or IS Testing', count(*) as 'Prod Review', 0 as 'UAT or Approval', 0 as 'Closed', 0 as 'Other', 0 as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'Prod Review'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN, 0 as 'Assigned or IS Testing', 0 as 'Prod Review', count(*) as 'UAT or Approval', 0 as 'Closed', 0 as 'Other', 0 as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'UAT or Approval'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN, 0 as 'Assigned or IS Testing', 0 as 'Prod Review', 0 as 'UAT or Approval', count(*) as 'Closed', 0 as 'Other', 0 as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'Closed'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN, 0 as 'Assigned or IS Testing', 0 as 'Prod Review', 0 as 'UAT or Approval', 0 as 'Closed', count(*) as 'Other', 0 as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'Closed'
group by TS_PLAN

union all
select TS_PLAN as TS_PLAN, 0 as 'Assigned or IS Testing', 0 as 'Prod Review', 0 as 'UAT or Approval', 0 as 'Closed', 0 as 'Other', count(*) as 'Unassigned'
from v_CH_STATE_GRP where TS_STATE_GRP = 'Other'
group by TS_PLAN
) as XX
group by TS_PLAN

===

 

[mercwrought]

Better yet did you copy my post or write you own and if you wrote your own did you use o's insted of zeros in the '0 as ...' portion?
by the way the the error is 'where Project_Name =' should say
'where Project_Type ='.

 

[PrgrmsAll]

I pasted yours and edited it. They are zeros. And I think I corrected the other part Project_Name -v- Project_Type.

Any other ideas? It is seeing 'Assigned or IS Testing' as a varchar, I believe.

 

[mercwrought]

ok, I think I see you problem you are using ' where they should not be. also notice you did the closed twice. i just left it alone. question is the entry in your TS_STATE_GRP column actuly 'Assigned or IS Testing' or is it 'Assigned' or could be 'IS Testing'.

here is a revision

select TS_PLAN, sum(Assigned ),sum(ProdReview),sum(UAT),sum(Closed), sum(Other),sum(Unassigned)
from
(
select TS_PLAN as TS_PLAN,count(*) as Assigned , 0 as ProdReview, 0 as UAT , 0 as Closed, 0 as Other, 0 as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'Assigned or IS Testing'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as Assigned , count(*) as ProdReview, 0 as UAT , 0 as Closed, 0 as Other, 0 as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'Prod Review'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as Assigned , 0 as ProdReview, count(*) as UAT , 0 as Closed, 0 as Other, 0 as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'UAT or Approval'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as Assigned , 0 as ProdReview, 0 as UAT ,count(*)as Closed, 0 as Other, 0 as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'Closed'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as Assigned , 0 as ProdReview, 0 as UAT ,  as Closed, count(*),0 as Other, 0 as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'Closed'
group by TS_PLAN

union all

select TS_PLAN as TS_PLAN,0 as Assigned , 0 as ProdReview, 0 as UAT , 0 as Closed,0  as Other, count(*) as Unassigned
from v_CH_STATE_GRP where TS_STATE_GRP = 'Other'
group by TS_PLAN

union all


) as XX
group by TS_PLAN

 

[manjarikosaraju]

SELECT State, PType, count(*) as Cnt
INTO #Temp
FROM TABLE1 --(TABLE1 contains ProjectId,ProjectName,Projecttype and State details)
GROUP BY State, PType
order by State, PType

--select * from #Temp

SELECT State,
SUM(CASE PType WHEN 'BedRoom' THEN Cnt ELSE 0 END) AS BedRoom,
SUM(CASE PType WHEN 'Kitchen' THEN Cnt ELSE 0 END) AS Kitchen,
SUM(CASE PType WHEN 'LivingRoom' THEN Cnt ELSE 0 END) AS LivingRoom,
SUM(CASE PType WHEN 'OutDoor' THEN Cnt ELSE 0 END) AS OutDoor
FROM #Temp
GROUP BY State
ORDER BY State

 

[PrgrmsAll]

mercwrought: without errors except for ") as XX" which is raising an "Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near ')'." error.

 

[PrgrmsAll]

manjarikosaraju:
your code worked like a charm. thank you very much.

mercwrought:
your code is basically working, too. with much appreciation - thank you.

 

[mercwrought]

oops the union all at the bottem. np