MySQL multiple table count query

Question

I have 3 tables

products
    productid (int)
    name (varchar)
    price (float)

sales
    salesid (int)
    productid (int)
    time (datetime)

links
    linkid (int)
    productid (int)
    link (text)

Now I need a query that can display as

ProductID      ProductName      Sales      Links
    1           ABC               10        8
    2           XYZ               15       12

I need all sales count and all links count for a particular product

How can I achieve this?

Thanks

What's this for, out of curiosity?

outis
– outis

2010-12-10 12:57:50 +00:00
Commented Dec 10, 2010 at 12:57 — outis
– outis, Commented Dec 10, 2010 at 12:57

Shakti Singh · Accepted Answer · 2010-12-10 13:29:41Z

3

SELECT p.productid, p.pname AS ProductName, 
       Count(DISTINCT s.salesid) as Sales, Count(DISTINCT l.linkid) as Links
 FROM products p
   LEFT JOIN sales s ON p.productid=s.productid
   LEFT JOIN links l ON p.products=l.productid 
GROUP BY p.productid

edited Dec 10, 2010 at 13:29

answered Dec 10, 2010 at 11:41

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

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

2 Comments

outis Over a year ago

What do you mean by "count same"? Do you mean the Sales and Links columns show the same count in each row?

I-M-JM Over a year ago

for first join, if it is left join then count come same

amelvin · Accepted Answer · 2010-12-10 11:57:06Z

0

You need to write a cross-tab query.

This walk-through on the mysql site should help.

Or like in this tutorial

edited Dec 10, 2010 at 11:57

answered Dec 10, 2010 at 11:37

amelvin

9,0714 gold badges40 silver badges59 bronze badges

2 Comments

amelvin Over a year ago

I've added a link to how the official mysql site solves this type of problem - that one is definitely optimized.

outis Over a year ago

Cross tab queries are to convert data in rows into columns. OP needs data aggregation, not a cross tab.

MartinodF · Accepted Answer · 2010-12-10 13:30:04Z

You can only use aggregate functions (like Count()) on the column specified in the GROUP BY clause, otherwise the aggregation is ambiguous. See the chapter "Ambiguous Groups" from the excellent book SQL Antipatterns for more reference.

In your case, the best option is to use two sub-queries:

SELECT p.productid as ProductID, p.pname AS ProductName, 
        (SELECT Count(*) FROM sales s WHERE p.productid=s.productid) as Sales,
        (SELECT Count(*) FROM links l WHERE p.productid=l.productid) as Links,
    FROM products p
    GROUP BY p.productid

Not the most efficient query ever written, but it's perfectly ok as long as the tables doesn't contain a huge number of rows and/or you run it as a periodic report and not real-time on live data.

The best thing to do is to test it for yourself and see if the performance is acceptable.

Collectives™ on Stack Overflow

MySQL multiple table count query

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related