MySQL Statement Combining a Join and a Count

MySQL statement combining a join and a count?

SELECT  fol.*
 ,      (       SELECT  COUNT(*)
                FROM    files           fil
                WHERE   fil.Folder      = fol.Folder
        )       AS      "Files"
FROM    folders         fol
WHERE   fol.userId      = 16

It's called a correlated subquery.

http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

Joining multiple Tables and using Count with MySQL

You might be missing GROUP BY...

SELECT p.*,u.*,count(distinct c.comment_id),count(distinct l.like_id)
FROM app_posts AS p
LEFT JOIN app_comments AS c ON c.post_id = p.post_id
LEFT JOIN app_user AS u ON u.user_id = p.user_id
LEFT JOIN app_likes AS l ON l.post_id = p.post_id
WHERE u.user_banned = 0
AND p.post_public = 1
GROUP BY p.post_id
ORDER BY p.post_date DESC

Note that MySQL lets you be sloppy with GROUP BY like this, but a lot of other databases would require you to break out the "p.*" into explicit MAX(p.post_id),MAX(p.post_content), etc.

MySQL joins and COUNT(*) from another table

MySQL use HAVING statement for this tasks.

Your query would look like this:

SELECT g.group_id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m USING(group_id)
GROUP BY g.group_id
HAVING members > 4

example when references have different names

SELECT g.id, COUNT(m.member_id) AS members
FROM groups AS g
LEFT JOIN group_members AS m ON g.id = m.group_id
GROUP BY g.id
HAVING members > 4

Also, make sure that you set indexes inside your database schema for keys you are using in JOINS as it can affect your site performance.

SQL Combining Counts with Joins

There's a couple things you should know with aggregate functions in SQL. First off, you need to do a GROUP BY if you're selecting an aggregate function. Second, any conditions involving aggregate functions are to be used with a HAVING clause rather than a WHERE.

The GROUP BY is to be applied to the column(s) you're selecting alongside any aggregate functions.

Here's a basic structure:

SELECT attribute1, COUNT(attribute2)
FROM someTable
GROUP BY attribute1
HAVING COUNT(attribute2) > 2;

Apply anything else you're using such as JOINS and ORDER BY and what not.

note: There's a certain order these clauses have to be in. Such as ORDER BY goes after HAVING, which comes after GROUP BY and so forth.

If I'm remembering correctly, the order of operations go:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

SQL Query With Count And Joining Table

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

Your problem is specifically that you need a left join:

select p.id, p.name, Count(t.id), p.price
from product p left join
     transaction t
     on p.id = t.product_id and
        t.customer_id = 1
group by p.price, p.name, p.id
order by p.name asc;

Note: you do not need the customer table. The id is in the transaction table.

COUNT Changes with Join on another Table MySQL

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

You need JOIN conditions:

SELECT e.name, e.eNR, COUNT(*), AVG(t.costs)
FROM employee e JOIN
     travel t
     ON e.name = t.name
GROUP BY e.name, e.eNR;

Additional comments:

• When you have more than one table in a query, use table aliases and qualified column names.
• Include all non-aggregated columns in the GROUP BY.
• name is a lousy foreign key. Usually, you want foreign keys to be unique (names may not be unique). And numbers are more efficient for indexing purposes.

Join two tables and get count of one but all results matching ID on other

The simplest way is with a correlated subquery:

SELECT e.name, e.venueAdresse, e.userID, e.tblEventID, e.venueCity, e.startTimeAndDateUnixTicks, e.displayStartTime, e.slots, e.eventPictureThumb, 
       (
         SELECT COUNT(*) 
         FROM `10561_12865_tblInvites` i
         WHERE i.tblEventID = e.tblEventID AND (i.status = 'accepted' OR i.status = 'host')
       ) counter
FROM `10561_12865_tblEvents` e 
WHERE e.userID  = ? 
ORDER BY e.createdUnixInt DESC 
LIMIT ?

Replace the ? placeholders with the parameters that you pass.

Multiple joins on the same table with counting in one query

I surmise that there is a 1:N relationship between people and likes.

One problem with your second query, as far as I can tell, is that the lwhom correlation of likes is joined to lwho via id=id. Basically lwhom is lwho. I'd recommend changing the ON clause for this correlation from lwhom.id = lwho.id to p.id = lwhom.whom.

The counts will still be affected by the JOINs, however. Supposing that you have an ID column in the likes table, though, you could then have each COUNT tally the distinct Like IDs per person – if not, consider just using COUNT(DISTINCT correlation.*) instead.

Digressions aside, the following should hopefully work:

SELECT p.id, name, 
   count(distinct lwho.id) delivered_likes, 
   count(distinct lwhom.id) received_likes, 
   count(distinct lmut.id) mutual_likes
FROM people AS p
LEFT JOIN likes AS lwho ON p.id = lwho.who
LEFT JOIN likes AS lwhom ON p.id = lwhom.whom
LEFT JOIN likes AS lmut ON lwhom.who = lmut.whom AND lwhom.whom = lmut.who
GROUP BY p.id,p.name;

I have an SQL Fiddle here.

---

Related Topics