• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Count not working

Dear Experts,

select count(*) as numrows
from datatable dt
inner join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
order by dt.date desc

This gives me 214 rows when I should be getting 142 rows

So I tried adding a group

select count(*) as numrows
from datatable dt
inner join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
group by dt.mainid
order by dt.date desc

but then it goes completely wrong.

Can someone please help as I need to get the count I get from this section

select count(*) as numrows
from datatable dt
order by dt.date desc

But I need to specify the categoryid which only exists in the categorytable.

please help.

I am using mysql 3.25 I think.
0
narmi2
Asked:
narmi2
2 Solutions
 
Bernard S.CTOCommented:
select unique count(dt.*) as numrows
from datatable dt
inner join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
order by dt.date desc
?? why this "order"?

select unique count( dt.mainid) as numrows
from datatable dt
inner join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
order by dt.date desc

0
 
pzurowskiCommented:
how does look rows which they shoudln't be? Is there any NULLs in ct.mainid or dt.mainid ?
select * as numrows
from datatable dt
inner join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
order by dt.date desc

Open in new window

0
 
narmi2Author Commented:
You're right I don't need the order.  However the above script is not working.  I am getting a query error.  How do I check what the error is?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Bernard S.CTOCommented:
select unique count( dt.mainid) as numrows
from datatable dt, categorytable ct
where (ct.categoryid = 10) AND (ct.mainid = dt.mainid)
0
 
narmi2Author Commented:
pzurowski,

No nulls, but there are repeating rows because each main id in datatable has many categoryids in categorytable
0
 
narmi2Author Commented:
That query does not work fibo,  I think mysql 3.23 does not support the "unique".
0
 
awking00Commented:
Use distinct keyword instead of unique.
0
 
raheel_lipsCommented:
Hey narmi2,

Try following query. I think this will get rid of your problem. I think your query need left join instead of inner join

Regards,
Raheel

select count(*) as numrows
from datatable dt
left join categorytable ct on ct.mainid = dt.mainid
where ct.categoryid = 10
group by dt.mainid

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now