Count same data. how? - SQL Server Forums

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

Count same data. how?

Author

Topic

sikharma13
Starting Member

44 Posts

Posted - 2013-11-16 : 05:35:29

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)

17689 Posts

Posted - 2013-11-16 : 05:41:31

what do you mean by "count the no_facility data" ?

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

sikharma13
Starting Member

44 Posts

Posted - 2013-11-16 : 05:45:17

no_facility values..
duplicate column values.

VFP9.0 via MySQL 5.0

sikharma13
Starting Member

44 Posts

Posted - 2013-11-16 : 05:54:14

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-16 : 05:59:10

[code]select entry_id, sum(case when name_of_school = 'no_facility' then 1 else 0 end)
from schools
group by entry_id[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-16 : 10:29:16

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

sikharma13
Starting Member

44 Posts

Posted - 2013-11-16 : 18:26:43

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-16 : 23:09:57

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
[spoiler]Time is always against us[/spoiler]

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 00:22:37

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
[spoiler]Time is always against us[/spoiler]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 01:01:50

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
[spoiler]Time is always against us[/spoiler]

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 06:47:59

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 09:00:12

[code]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[/code]

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

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 09:47:38

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
[spoiler]Time is always against us[/spoiler]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 09:52:01

quote:
make an output for me?

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

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

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 09:52:31

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

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 09:54:25

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 09:57:24

[code]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[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 09:59:29

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
[spoiler]Time is always against us[/spoiler]

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 10:01:22

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 10:14:42

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
[spoiler]Time is always against us[/spoiler]

sikharma13
Starting Member

44 Posts

Posted - 2013-11-17 : 10:15:24

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

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources