SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count same data. how?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/16/2013 :  05:35:29  Show Profile  Reply with Quote
i have table schools and i have a column name_of_school

name_of_school
no facility
adamson_high
la_salle_high
no facility


i want to count the no_facility data. how?

VFP9.0 via MySQL 5.0

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/16/2013 :  05:41:31  Show Profile  Reply with Quote
what do you mean by "count the no_facility data" ?


KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/16/2013 :  05:45:17  Show Profile  Reply with Quote
no_facility values..
duplicate column values.

VFP9.0 via MySQL 5.0
Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/16/2013 :  05:54:14  Show Profile  Reply with Quote
follow up question.. if they are in the same entry id also like..


entry_id====name_of_school===
0000001=====No_facility=====
0000001=====lasalle_high====
0000001=====no_facility=====
0000002=====adamson_high====
0000002=====no_facility=====

expected output..
0000001=====2
0000002=====1

thanks alot!

VFP9.0 via MySQL 5.0
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/16/2013 :  05:59:10  Show Profile  Reply with Quote
select entry_id, sum(case when name_of_school = 'no_facility' then 1 else 0 end)
from   schools 
group by entry_id



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/16/2013 :  10:29:16  Show Profile  Reply with Quote
or simply


select entry_id, count(*)
from   schools 
where name_of_school = 'no_facility'
group by entry_id

unless you want to display the entry_id values without any no_facility present.

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

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/16/2013 :  18:26:43  Show Profile  Reply with Quote
i have table payroll and has column number of eligible for education

entry_id===no_eli_educ
00000001========2=====
00000002========3=====
00000003========0=====


and i havecolumn name_of_schools in table schools

entry_id===name_of_schools
00000001===adamson high
00000002===lasalle high
00000002===no facility
00000002===guanlun high
00000003===no facility

i want to add a column number of no school in my payroll table

wherein this is my expected output

number_of_no_school
00000001===0
00000002===1
00000003===0

since entry_id 00000003 has no eligible for education
it will also considered no number_of_no_school as 0..

is it possible?? thanks!



VFP9.0 via MySQL 5.0

Edited by - sikharma13 on 11/16/2013 18:46:47
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/16/2013 :  23:09:57  Show Profile  Reply with Quote
can you explain do you obtain this result ?
wherein this is my expected output

number_of_no_school
00000001===0
00000002===1
00000003===0



KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  00:22:37  Show Profile  Reply with Quote
quote:
Originally posted by khtan

can you explain do you obtain this result ?
wherein this is my expected output

number_of_no_school
00000001===0
00000002===1
00000003===0



KH
Time is always against us





no sir, this is what i need to obtain.. and i just dont know how..
im confused since it uses two tables... thanks

VFP9.0 via MySQL 5.0
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  01:01:50  Show Profile  Reply with Quote
i mean what is the logic behind to get number_of_no_school = 0 for entry 1 and etc ?

You have to explain to us the logic behind.


KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  06:47:59  Show Profile  Reply with Quote
uhmm for me to get the number of no school.. i need to
connect tables number of eligible for number of no school..
simply like..

for example.. number of eligible for education is
entry_id
0000001 - it has 2 eligible children for education in this entry id
0000002- has 3 children eligible
0000003- has no eligible

entry_id===name_of_schools
00000001===adamson high
00000001===ateneo high
00000002===lasalle high
00000002===no facility
00000002===guanlun high
00000003===no facility

wherein this is my expected output

number_of_no_school
00000001=== adamson high and ateneo so it has 2 schools.. meaning number of no school is equals to zero
00000002=== lasalle high and no facility, meaning it has 1(one) number of no school is equals to one..
00000003=== it has no facility meaning it has no school which is equal to 1 number of no school..but since the entry id 0000003
has no eligible for education.. it will consider it equals to 0
number of no school..


thanks
VFP9.0 via MySQL 5.0

Edited by - sikharma13 on 11/17/2013 06:51:10
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  09:00:12  Show Profile  Reply with Quote
select s.entry_id, sum(case when p.no_eli_educ > 0 
                            then case when name_of_schools <> 'no facility' then 1 else 0 end
                            else 0 
                            end)
from   schools s
       inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id



KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  09:47:38  Show Profile  Reply with Quote
quote:
Originally posted by khtan

select s.entry_id, sum(case when p.no_eli_educ > 0 
                            then case when name_of_schools <> 'no facility' then 1 else 0 end
                            else 0 
                            end)
from   schools s
       inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id



KH
Time is always against us





sir can u please make an output for me? thanks..
seems like i couldnt get my expected results,,, thanks anyway :)

VFP9.0 via MySQL 5.0
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  09:52:01  Show Profile  Reply with Quote
quote:
make an output for me?

Sorry, don't quite really understand what do you want here


KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  09:52:31  Show Profile  Reply with Quote
i cant seem to find outputs that has no eligible for educ..
only those with eligibility is the only one i can see..
sorry for my bad english..

VFP9.0 via MySQL 5.0
Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  09:54:25  Show Profile  Reply with Quote
entry_id===no_eli_educ
00000001========2=====
00000002========3=====
00000003========0=====

expected result
number_of_no_school
00000001===0
00000002===1
00000003===0

your query's output..

00000001===0
00000002===1


the 0000003 is missing..

VFP9.0 via MySQL 5.0
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  09:57:24  Show Profile  Reply with Quote
select s.entry_id, sum(case when p.no_eli_educ > 0 
                            then case when name_of_schools = 'no facility' then 1 else 0 end
                            else 0 
                            end)
from   schools s
       inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id



KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  09:59:29  Show Profile  Reply with Quote
quote:
Originally posted by sikharma13

entry_id===no_eli_educ
00000001========2=====
00000002========3=====
00000003========0=====

expected result
number_of_no_school
00000001===0
00000002===1
00000003===0

your query's output..

00000001===0
00000002===1


the 0000003 is missing..

VFP9.0 via MySQL 5.0



do you have a row with entry_id = 0000003 in the schools table ?


KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  10:01:22  Show Profile  Reply with Quote
no_eli_educ > 0

only those eligibles > 0 are displayed..
how about those no_eli_educ = 0



thank you so much for your effort!

VFP9.0 via MySQL 5.0
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 11/17/2013 :  10:14:42  Show Profile  Reply with Quote
quote:
Originally posted by sikharma13

no_eli_educ > 0

only those eligibles > 0 are displayed..
how about those no_eli_educ = 0



thank you so much for your effort!

VFP9.0 via MySQL 5.0



not really. only those with no_eli_educ > 0 are counted as 1. All entry id are display


KH
Time is always against us

Go to Top of Page

sikharma13
Starting Member

Philippines
40 Posts

Posted - 11/17/2013 :  10:15:24  Show Profile  Reply with Quote
sir ive come up with a solution..

select s.entry_id, sum(case when p.no_eli_educ > 0
then case when name_of_schools = 'no facility' then 1 else 0 end
else 0
end)
from schools s
inner join payroll p on s.entry_id = p.entry_id
group by s.entry_id


update payroll set number_of_no_schools

"your query here"

where p.no_eli_educ > 0

and that will answer my question... thank you so much! hehehehe

VFP9.0 via MySQL 5.0
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000