narmi2
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
select unique count( dt.mainid) as numrows
from datatable dt, categorytable ct
where (ct.categoryid = 10) AND (ct.mainid = dt.mainid)
from datatable dt, categorytable ct
where (ct.categoryid = 10) AND (ct.mainid = dt.mainid)
ASKER
pzurowski,
No nulls, but there are repeating rows because each main id in datatable has many categoryids in categorytable
No nulls, but there are repeating rows because each main id in datatable has many categoryids in categorytable
ASKER
That query does not work fibo, I think mysql 3.23 does not support the "unique".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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