how to add the counts to other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to add the counts to other table

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-09 : 10:23:43

I have a question on stored procedure. Here is the scenario:
table1 has a column of status which nay have data of three status for 'not ready', 'processing', and 'overdue'. table2 has columns of NotReadyCount, ProcessingCount and OverdueCount which keep the sum of these three status in table1 to let use know how many 'not ready' and how many 'overdue', how many 'processing' are there. Now I need to check all the records in table1 to pick up these three status and add all the count of each status and save the sum into table2's three columns accordingly. I need a stored procedure to run this. Can anyone show me the flow of the coding? Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 10:28:03

[code]
update t2
set NotReadyCount = t1.NotReadyCount,
ProcessigCount = t1.ProcessingCount,
OverdueCount = t1.OverdueCount
from table2 t2
cross join
(
select NotReadyCount = count(case when status = 'not ready' then 1 end),
ProcessingCount = count(case when status = 'processing' then 1 end),
OverdueCount = count(case when status = 'overdue' then 1 end)
from table1
) t1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 11:23:16

[code]
update t2
set NotReadyCount = t1.[not ready],
ProcessigCount = t1.[processing],
OverdueCount = t1.[overdue]
from table2 t2
inner join (select [not ready],[processing],[overdue]
from table1
pivot(count(1) for status in ([not ready],[processing],[overdue]))p
)t1
on 1=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-09 : 16:56:42

Hi Khan,
Thank you so much for the quick reply. It works but I may not described the requirement clear. When I got sum I need insert it into table 2 as a new record. The table2 is empty before the process. The situation looks like this:

Table1:

ID status Location
---------------------------------------------------
1 Not Ready A
2 Not Ready B
3 Processing A
4 Overdue C
5 Not Ready A
6 Overdue B
7 Processing A
8 Not Ready C

Table2

ID Location NotReadyCount ProcessingCount OverdueCount
------------------------------------------------------------------------
1 A 2 2 0
2 B 1 0 1
3 C 1 0 1

I need count how many the same status in the same location then insert this into table 2 as a new row with sum.

For visakh16's solution I tried but got syntax error at 1=1. I'll keep try. Thanks too.
Appreciate your help, gents.

(I don't know why it looks like this, it's good in notepad)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 18:48:50

[code]
insert into table2 (location, NotReadyCount, ProcessigCount, OverdueCount)
select Location,
NotReadyCount = count(case when status = 'not ready' then 1 end),
ProcessingCount = count(case when status = 'processing' then 1 end),
OverdueCount = count(case when status = 'overdue' then 1 end)
from table1 t1
where not exists
(
select * from table2 t2 where t1.location = t2.location
)
group by Location

update t2
set NotReadyCount = t1.NotReadyCount,
ProcessigCount = t1.ProcessingCount,
OverdueCount = t1.OverdueCount
from table2 t2
inner join
(
select Location,
NotReadyCount = count(case when status = 'not ready' then 1 end),
ProcessingCount = count(case when status = 'processing' then 1 end),
OverdueCount = count(case when status = 'overdue' then 1 end)
from table1
group by Location
) t1 on t1.Location = t2.Location
[/code]

edited typo error in red

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 18:53:32

also try

quote:

Originally posted by visakh16


update t2
set    NotReadyCount   = t1.[not ready], 
       ProcessigCount  = t1.[processing],
       OverdueCount    = t1.[overdue]
from   table2 t2
inner join (select location, [not ready],[processing],[overdue]
            from (select location, status from table1) t1
            pivot(count(status) for status in ([not ready],[processing],[overdue]))p
            )t1
on t2.location = t1.location

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KH
[spoiler]Time is always against us[/spoiler]

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-09 : 23:42:03

Thanks Khtan. But should I run "insert into table2 ..." only or with update t2? Either way I got error msg:
"The multi-part identifier "t2.location" could not be bound." Plz advise.

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-09 : 23:52:51

Actually I think
"not exists
(
select * from table1 t1 where t1.location = t2.location
)" part should return a false, so I changed where to "t1.location = 'x' " which not exists, it works.

I wonder if I can use While statement to do the comparison then do the insert inside the loop. Is it possible.
Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 00:28:46

quote:
Originally posted by allan8964

Thanks Khtan. But should I run "insert into table2 ..." only or with update t2? Either way I got error msg:
"The multi-part identifier "t2.location" could not be bound." Plz advise.


insert into table2 (location, NotReadyCount, ProcessigCount, OverdueCount)
select  Location,
        NotReadyCount   = count(case when status = 'not ready' then 1 end),
        ProcessingCount = count(case when status = 'processing' then 1 end),
        OverdueCount    = count(case when status = 'overdue' then 1 end)
from    table1 t
where   not exists
        (
            select * from table2  where location = t.location
        )
group by Location

update t2
set    NotReadyCount   = t1.NotReadyCount, 
       ProcessigCount  = t1.ProcessingCount,
       OverdueCount    = t1.OverdueCount
from   table2 t2
       inner join
       (
           select  Location,
                   NotReadyCount   = count(case when status = 'not ready' then 1 end),
                   ProcessingCount = count(case when status = 'processing' then 1 end),
                   OverdueCount    = count(case when status = 'overdue' then 1 end)
           from    table1
           group by Location
       ) t1 on t1.Location = t2.Location

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 03:12:44

quote:
Originally posted by allan8964

Thanks Khtan. But should I run "insert into table2 ..." only or with update t2? Either way I got error msg:
"The multi-part identifier "t2.location" could not be bound." Plz advise.

i have edited my post in red. Or use the one edited by Visakh

KH
[spoiler]Time is always against us[/spoiler]

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-11 : 09:50:18

Thanks gentlemen!!!
But I still have problem. For Location column in table1 I have 'A', 'B', and 'C'. More I have 'AB', and 'ABB' which should be summed with 'A'. In other word, 'A', 'AB' and 'ABB' belong to one location, not three. That means I need to put sum of the counts of 'A', 'AB' and 'ABB' as one record in table2, not three. How can I do that?
Thanks again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-11 : 09:57:38

how do you determine that what are the columns are suppose to group together ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 09:58:37

[code]
insert into table2 (location, NotReadyCount, ProcessigCount, OverdueCount)
select case when Location in ('A','AB','ABB') then 'A'
else Location
end,
NotReadyCount = count(case when status = 'not ready' then 1 end),
ProcessingCount = count(case when status = 'processing' then 1 end),
OverdueCount = count(case when status = 'overdue' then 1 end)
from table1 t
where not exists
(
select * from table2 where location = case when t.Location in ('A','AB','ABB') then 'A'
else t.Location
end
)
group by case when Location in ('A','AB','ABB') then 'A'
else Location
end

update t2
set NotReadyCount = t1.NotReadyCount,
ProcessigCount = t1.ProcessingCount,
OverdueCount = t1.OverdueCount
from table2 t2
inner join
(
select case when Location in ('A','AB','ABB') then 'A'
else Location
end AS Location,
NotReadyCount = count(case when status = 'not ready' then 1 end),
ProcessingCount = count(case when status = 'processing' then 1 end),
OverdueCount = count(case when status = 'overdue' then 1 end)
from table1
group by case when Location in ('A','AB','ABB') then 'A'
else Location
end
) t1 on t1.Location = t2.Location
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-11-16 : 10:25:33

You guys are so helpful!!! Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:37:31

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources