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
 Last insert problem. please help! :) [SOLVED!]

Author  Topic 

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 04:16:04
please help..
i have one table which is py_abcdeeh..

i have one table and here are the following column variables.
province,municipality,barangay,household_id,entry_id,no_eli_for_educ_grant

i need to insert two queries in one table.
here are the queries..

insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id) select province,municipality,barangay,household_id,entry_id from m_abcdeeh where grantee = 'yes'

insert into py_abcdeeh(no_eli_for_educ_grant)select sum(case when eligible_for_educ_grant = 'yes' then 1 else 0 end) as count FROM m_abcdeeh GROUP BY household_id

here is the result..
----province----muni----brgy----hhid----entry_id----
----province----muni----brgy----hhid----entry_id----
----province----muni----brgy----hhid----entry_id----
----province----muni----brgy----hhid----entry_id----
-----------------------------------------------------3
-----------------------------------------------------3
-----------------------------------------------------4
-----------------------------------------------------1

my expected result is ..

----province----muni----brgy----hhid----entry_id----3
----province----muni----brgy----hhid----entry_id----3
----province----muni----brgy----hhid----entry_id----4
----province----muni----brgy----hhid----entry_id----1


thanks for the help! and im hoping to contribute also here in this useful site! :))



VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 04:26:57
Again in SQL Server this would work. wont know about MySQL


insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,no_eli_for_educ_grant)
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id ,m2.count
from m_abcdeeh m1
inner join (select household_id,sum(case when eligible_for_educ_grant = 'yes' then 1 else 0 end) as count
FROM m_abcdeeh
GROUP BY household_id
)m2
on m2.household_id = m1.household_id
where m1.grantee = 'yes'

Try and see if it works for you
Otherwise try in some MySQL forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 04:30:59
quote:
Originally posted by visakh16

Again in SQL Server this would work. wont know about MySQL


insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,no_eli_for_educ_grant)
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id ,m2.count
from m_abcdeeh m1
inner join (select household_id,sum(case when eligible_for_educ_grant = 'yes' then 1 else 0 end) as count
FROM m_abcdeeh
GROUP BY household_id
)m2
on m2.household_id = m1.household_id
where m1.grantee = 'yes'

Try and see if it works for you
Otherwise try in some MySQL forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Flawless!!, That solved my assignment for tomorrow! thanks! :)

VFP9.0 via MySQL 5.0
Go to Top of Page
   

- Advertisement -