I have three tables:
categories - categories
|------|------------|
| id | nombre |
|------|------------|
| 1 | categoria1 |
| 2 | categoria2 |
| 3 | categoria3 |
|------|------------|
items - blog articles
|------|-----------------|----------------|
| id | category_id | item_title |
|------|-----------------|----------------|
| 1 | 2 | lo q sea.. |
| 2 | 1 | lo q sea.. |
| 3 | 3 | lo q sea.. |
| 4 | 2 | lo q sea.. |
|------|-----------------|----------------|
views_unique - unique views for blog posts
|----|-----------|
| id | item_id |
|----|-----------|
| 1 | 2 |
| 2 | 3 |
| 3 | 3 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
|----|-----------|
Desired result I want to get a table like this:
|--------------------------|-----------------------|---------------------|
| nombre_de_categorias | cantidad_de_items | visitas |
|--------------------------|-----------------------|---------------------|
| categoria1 | 1 | 3 |
| categoria2 | 2 | 2 |
| categoria3 | 1 | 2 |
|--------------------------|-----------------------|---------------------|
I have tried everything. My last try:
SELECT DISTINCT c.name,
(SELECT COUNT(*) FROM items i
WHERE c.id = i.category_id) AS cantidad_de_items,
(SELECT COUNT(*) FROM views_unique vu
WHERE i.category_id = c.id ) AS visitas
FROM categories c
ORDER BY visitas DESC;
The number of items is good for me, but visits: no.
You can use
JOIN
,COUNT
andSUM
to relate the data toGROUP
two.I separate the different parts so you can see how it is assembled:
To know the names of the categories
To know how many items each category has
To know how many visits each item has
Join and add to know how many visits per category
We join the results to have the names of the categories, we order by visits
A fiddle http://www.sqlfiddle.com/#!9/730c6c/3