[RealMadrid]

Hi:

I am trying to find a solution to the following problem. I have the table shown below. This tables represent entries in a list, where the ones with the same "numorden" value represent just one entry in the list. Furthermore if any line corresponding to an entry in the list has borrado=1 it is not shown in the list.

What I now want is to get the count of entries I have in my list.

I found a way for retrieving the entries is:

select * from apuntes group by numorden HAVING sum(borrado)=0

But this gives me a list and I only want the count of entries because the table can be very big and I dont want that each time I need the number of entries I get the entire result.

I haven't been able to get count() working as it still gets different lines (probably because of the GROUP BY clause).

Any suggestions?

Thx in advance.


+-----+----------+---------+
| uid | numorden | borrado |
+-----+----------+---------+
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 6 | 5 | 0 |
| 7 | 5 | 0 |
| 8 | 8 | 0 |
| 9 | 9 | 0 |
| 10 | 11 | 0 |
| 11 | 11 | 0 |
| 12 | 11 | 1 |
| 13 | 13 | 0 |
+-----+----------+---------+

[queritor]

This will do what you want in version 4.0.nn

Code:
SELECT count(distinct numorden)
   FROM apuntes
   WHERE borrado = 0;

[RealMadrid]

Thanks for your reply.

I have tried it and it also works on my 3.23.xx server. The thing is that the result is 9 where it should be 8.

My problem is that you cannot just use that syntax as it includes the rows marked with numorden=11. As one of them has borrado=1 the lines with numorden=11 should not be counted even though other have 0. Only those where all have 0 should be counted.

In natural language what I want the MySQL server to do is:

- Group together all lines with the same value in numorden.
- Discard all groups where at least one line has borrado =1
- Return the count (just the number, not the lines) of remaining groups.

[queritor]

Originally posted by RealMadrid:
Thanks for your reply.

I have tried it and it also works on my 3.23.xx server. The thing is that the result is 9 where it should be 8.

My problem is that you cannot just use that syntax as it includes the rows marked with numorden=11. As one of them has borrado=1 the lines with numorden=11 should not be counted even though other have 0. Only those where all have 0 should be counted.

oh yeah... sorry.

I'm not sure this can be done without using merge tables or sub-queries, neither of which is available in MySQL 3.23. I'm assuming you need to do this in *one* query only?

If you find a way to do it, I'd be interested in seeing the solution.

[RealMadrid]

Well, yes the ideal solution would be to do it in one query in 3.xx.

But if it is not possible my solution is to count the groups and then count the lines with borrado=1 and do a substraction. This at least will do it for me.

Thanks for your help. I'll post if I find a solution.

[PBG4 User]

When working with COUNT() in SQL, you need to use the ORDER BY clause, not the GROUP BY clause.

[queritor]

Originally posted by PBG4 User:
When working with COUNT() in SQL, you need to use the ORDER BY clause, not the GROUP BY clause.

That is not correct. The ORDER BY clause simply orders the result set and has nothing to do with aggregates.

count() follows the same rules as all other aggregate functions such as sum(), avg(), max() etc. When using an aggregate function, you must have a GROUP BY clause. Within that GROUP BY, you must include all returned columns that are not aggregated.

Having said that, some databases allow you to ommit returned values from the GROUP BY clause if the values are unique within the existing result set. MySQL version 4.0 allows this as does Oracle 9i.

[PBG4 User]

You are absolutely correct. My bad.

Slinks off to corner to re-learn SQL. I really don't know what I was thinking when I posted earlier.

[PBG4 User]

I have tried it and it also works on my 3.23.xx server. The thing is that the result is 9 where it should be 8.

My problem is that you cannot just use that syntax as it includes the rows marked with numorden=11. As one of them has borrado=1 the lines with numorden=11 should not be counted even though other have 0. Only those where all have 0 should be counted.

Looking at this criteria, it seems you need to use the EXISTS keyword in your WHERE clause like so (this may not be correct but I believe it's a good start):

SELECT count(distinct numorden)
FROM apuntes
WHERE NOT EXISTS
(SELECT numorden FROM apuntes WHERE borrado = 1);

This will drop the whole group where numorden=11 since there EXISTS a record WHERE borrado = 1.

This should return 8 records.

[cheerios]

Code:
SELECT count(distinct numorden)
   FROM apuntes
   GROUP BY borrado
   HAVING borrado != 0

would that do it? (newbie @ sql, but it sounds right to me)

[RealMadrid]

Thanks to all for your help!

In reply to "PBG4 User" probably your solution could work but it does not execute in my 3.23.xx MySQL as I think that nested selects are part of 4.x.

And the answer to cheerios is that unfortunately it does not work. Using a column in Group by needs it to be specified in the column description in select.

It should be something more like:

select * from apuntes group by numorden HAVING sum(borrado)=0;

[cheerios]

eh, like I said, newbie @ SQL... thought I'd take a swing... good luck