COUNT Statement problem.. please help! [SOLVED!]

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 Statement problem.. please help! [SOLVED!]

Author

Topic

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 02:44:58

can you please help me?
right now im making a program that uses mysql query browser..
here's my structure.. my table name is m _abcdeeh

--------household_id------------entry_id--------name-------------eligibleeducgrant--------age
036912345-1234-00001--------12345678--------juan delacruz---------Yes-----------------14
036912345-1234-00001--------12323232--------pedro delacruz--------yes-----------------4
036916343-3123-00022--------23124152--------maria delacruz--------NULL----------------18
036913441-2123-00013--------93131152--------mary dela cruz--------yes-----------------10

im using count(*) statement.. and i want to count the number of eligible for educ. my range for education
are ages 3 - 14 years old.

this is my query
select household_id,count(*) from m_abcdeeh where eligibleeducgrant = 'yes' group by household_id

this is the result
household_id count(*)
036912345-1234-00001 2
036913441-2123-00013 1

but this is my expected result.
household_id count(*)
036912345-1234-00001 2
036913441-2123-00013 1
036916343-3123-00022 0

how can a null value return to zero value? help me please...
to be exact.. if yes it will count... if NULL... it must be zero.. thanks

VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 02:59:34

this is ms sql server forum not mysql. So there are not enough experts in MySQL.

In SQL Server you can do like this


SELECT household_id,
sum(case when eligibleeducgrant = 'yes' then 1 else 0 end) as [count(*)]
FROM m _abcdeeh
GROUP BY household_id

try this in MySQL and if this doesnt work try your luck in some MySQL forums like www.dbforums.com

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-10-25 : 03:02:27

select household_id,count(eligibleeducgrant) from m_abcdeeh where eligibleeducgrant = 'yes' OR eligibleeducgrant IS NULL group by household_id

--
Chandu

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 03:14:42

Thank you so much for the reply. :)

VFP9.0 via MySQL 5.0

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 03:18:21

quote:
Originally posted by bandi

select household_id,count(eligibleeducgrant) from m_abcdeeh where eligibleeducgrant = 'yes' OR eligibleeducgrant IS NULL group by household_id

--
Chandu

Sir, still not returning zero result.. any alternate idea pls? im stucked in this query for almost 3 days..
thanks for your help...

VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 03:24:08

quote:
Originally posted by sikharma13

quote:
Originally posted by bandi

select household_id,count(eligibleeducgrant) from m_abcdeeh where eligibleeducgrant = 'yes' OR eligibleeducgrant IS NULL group by household_id

--
Chandu

Sir, still not returning zero result.. any alternate idea pls? im stucked in this query for almost 3 days..
thanks for your help...

VFP9.0 via MySQL 5.0

wat about my version?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 03:29:09

here's the full illustration


declare @m_abcdeeh table
(household_id varchar(50),
entry_id int,
name varchar(100),
eligibleeducgrant varchar(3),
age int
)
insert @m_abcdeeh
values
('036912345-1234-00001',12345678,'juan delacruz','Yes',14),
('036912345-1234-00001',12323232,'pedro delacruz','yes',4),
('036916343-3123-00022',23124152,'maria delacruz',NULL,18),
('036913441-2123-00013',93131152,'mary dela cruz','yes',10)


SELECT household_id,
sum(case when eligibleeducgrant = 'yes' then 1 else 0 end) as [count(*)]
FROM @m_abcdeeh
GROUP BY household_id


output
---------------------------------
household_id	        count(*)
---------------------------------
036912345-1234-00001	2
036913441-2123-00013	1
036916343-3123-00022	0

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-25 : 03:31:01

Declare @Temp Table(household_id varchar(100),entry_id bigint,name varchar(20),eligibleeducgrant char(10),age int)

Insert @Temp

Select '036912345-1234-00001',12345678,'juan delacruz','Yes',14
union all select '036912345-1234-00001',12323232,'pedro delacruz','yes',4
union all select '036916343-3123-00022',23124152,'maria delacruz',NULL,18
union all select '036913441-2123-00013',93131152,'mary dela cruz','yes',10

Select Household_id
, Count(Case When eligibleeducgrant = 'Yes' Then 1 Else Null End ) AS 'Count(*)'
From @Temp
Where eligibleeducgrant = 'Yes' or eligibleeducgrant is null
Group by Household_id

veeranjaneyulu

sikharma13
Starting Member

44 Posts

Posted - 2013-10-25 : 03:37:04

quote:
Originally posted by visakh16

this is ms sql server forum not mysql. So there are not enough experts in MySQL.

In SQL Server you can do like this

SELECT household_id,
sum(case when eligibleeducgrant = 'yes' then 1 else 0 end) as [count(*)]
FROM m _abcdeeh
GROUP BY household_id

try this in MySQL and if this doesnt work try your luck in some MySQL forums like www.dbforums.com

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

Thank you so much sir!
your query has an error in mysql..

but when i try this query..
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

it worked!.. all i did is change the [count(*)] into count...

next question... where is the thanks button here?? :))

VFP9.0 via MySQL 5.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 04:00:00

no thanks button...just post thanks and its enough

You can also add [Resolved] to title of thread to indicate your issue is solved

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 04:01:01

quote:
Originally posted by VeeranjaneyuluAnnapureddy

Declare @Temp Table(household_id varchar(100),entry_id bigint,name varchar(20),eligibleeducgrant char(10),age int)

Insert @Temp

Select '036912345-1234-00001',12345678,'juan delacruz','Yes',14
union all select '036912345-1234-00001',12323232,'pedro delacruz','yes',4
union all select '036916343-3123-00022',23124152,'maria delacruz',NULL,18
union all select '036913441-2123-00013',93131152,'mary dela cruz','yes',10

Select Household_id
, Count(Case When eligibleeducgrant = 'Yes' Then 1 Else Null End ) AS 'Count(*)'
From @Temp
Where eligibleeducgrant = 'Yes' or eligibleeducgrant is null
Group by Household_id

veeranjaneyulu

whats the point in repeating what others suggested long back?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-28 : 05:36:59

quote:
Originally posted by VeeranjaneyuluAnnapureddy

Declare @Temp Table(household_id varchar(100),entry_id bigint,name varchar(20),eligibleeducgrant char(10),age int)

Insert @Temp

Select '036912345-1234-00001',12345678,'juan delacruz','Yes',14
union all select '036912345-1234-00001',12323232,'pedro delacruz','yes',4
union all select '036916343-3123-00022',23124152,'maria delacruz',NULL,18
union all select '036913441-2123-00013',93131152,'mary dela cruz','yes',10

Select Household_id
, Count(Case When eligibleeducgrant = 'Yes' Then 1 Else Null End ) AS 'Count(*)'
From @Temp
Where eligibleeducgrant = 'Yes' or eligibleeducgrant is null
Group by Household_id

veeranjaneyulu

SUM approach is better than COUNT http://beyondrelational.com/modules/2/blogs/70/posts/19240/conditional-aggregation-sum-vs-count.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources