MySQL multiple count in different table in one query

Question

I'm coming to you because I currently have a problem with a SQL query.

Let me explain :

I have two tables, the first say tableA, record all the different terminals that connected to my application:

The COIDTE field corresponds to the identifier of the terminal (unique).

Then I have a second table say tableB which records all connections to the application and stores the identifier of the connected terminal (COIDTE) and the jailbreak status of the terminal (if 0 non-jailbreak terminal).

I am asked to return the number of trusted terminal, knowing that a terminal is said to trust if there has been at least 5 connections or jailbreak status is 0.

I can not do that with one request ... I tried several things but I can not combine everything .. thx for help :)

i tried :

Select count(a.COIDTE),
From TableA a, TableB b
Where a.COIDTE= b.COIDTE
HAVING ( Select count( distinct COIDTE ) from tableA where CTJDTE ='0'

left join the two tables and select the counts, group by and use having — dbajtr
– dbajtr, Commented Feb 9, 2018 at 9:57
i updated my answer but my query doesn't work.. @dbajtr can you explain left join and having ? — Léo R.
– Léo R., Commented Feb 9, 2018 at 10:06

cdaiga · Accepted Answer · 2018-02-09 15:06:53Z

1

Try this:

SELECT COUNT(*) FROM TableB GROUP BY COIDTE HAVING COUNT(*)>4 OR SUM(CTJBTE)=0;

edited Feb 9, 2018 at 15:06

answered Feb 9, 2018 at 10:07

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Léo R. Over a year ago

This doesn't work.. expected result is a count of correct device

cdaiga Over a year ago

I just adjusted the query. Try again!

Léo R. Over a year ago

Thx, this works, however i think there is a problem in the query i have 1751 different device, if i just run : SELECT count() FROM TableB GROUP BY COIDTE HAVING COUNT()>4) this return me : 66. so there is only 66 Device with at least 5 No Jailbreak connexion ? But with your query i get 1240 ?

Léo R. Over a year ago

Thx for you answer, i tried a mix with your differents answers and this finaly works =D

Collectives™ on Stack Overflow

MySQL multiple count in different table in one query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related