Count Column in multiple inner join

Question

i want to get count of groupby (bebleiotheque.type), its work but whene i add multiple many2many fields to get multiple books or multiple employer for each bebeliotheque row, my count get rise

howa to get real count of groupby (bebeliotheque type)

Example on db<>fiddle

take a look in my app list bebeliotheque, i regroup all same bebeliotheque name but I have an incorrect (count)

preview

image > http://rezigdev.com/image_202112522328.jpg

video > https://rezigdev.com/preview.mp4

sql query

SELECT 
        bibliotheque.name, GROUP_CONCAT(book.name)  as books,  GROUP_CONCAT(employers.name)  as employers, COUNT(bibliotheque.name)
FROM 
        bibliotheque
        left join 
          x_x_bibliotheque_book 
          ON x_x_bibliotheque_book.bibliotheque_id = bibliotheque.id
        left join 
          book 
          ON x_x_bibliotheque_book.book_id = book.id
          
        left join 
          x_x_bibliotheque_employers 
          ON x_x_bibliotheque_employers.bibliotheque_id = bibliotheque.id
        left join 
          employers 
          ON x_x_bibliotheque_employers.employer_id = employers.id
          
          
Group BY 
        bibliotheque.name

again , in plain english try to explain what's your logic, what are you trying to count? explain it — eshirvana, Dec 03 '21 at 17:34
I'n not sure wether I understand what you want, but I try: If you replace the COUNT(...) in you statement by `COUNT(distinct book.name)` would this bring the expected result? Because you want to count the number of books in each bib? — h.m.i.13, Dec 04 '21 at 16:18
Please put everything needed to ask your question in your post, not just at a link. Please clarify via edits, not comments. Please use standard spelling & punctuation. Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. For code questions give a [mre]. — philipxy, Dec 05 '21 at 22:03
Duplicate of [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Dec 05 '21 at 22:10

score -1 · Accepted Answer · answered Dec 05 '21 at 21:18

-1

Solution : COUNT(distinct bibliotheque.id)

answered Dec 05 '21 at 21:18

Rezigdev.com

33
5

Count Column in multiple inner join

1 Answers1