# Modificar un reporte para incluir las métricas de Replies y Solved
**URL:** https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [19 Enero, 2024 10:19 UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195 "2024-01-19T10:19:18Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@Danny\_Dainton](https://meta.discourse.org/u/Danny_Dainton)
#### Post date: [19 Enero, 2024 10:19 UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195/1 "2024-01-19T10:19:18Z")
Hola equipo 👋,
Estoy buscando ayuda para modificar esta consulta SQL para que también incluya el número de soluciones para las categorías y el número de respuestas:
```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT c.id category_id, COUNT(DISTINCT(t.id)) topics, COUNT(p.id) posts, sum(p.like_count) likes, sum(p.reads) reads
FROM categories c
INNER JOIN topics t ON (t.category_id = c.id)
INNER JOIN posts p ON (p.topic_id = t.id AND p.post_type = 1)
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
```
Actualmente, la salida es como esta para esa consulta, lo cual es muy útil, pero me gustaría incluir esas otras métricas.
He intentado modificarla, pero mis habilidades de SQL son muy pobres. Si alguien pudiera orientarme o ayudarme con la consulta, estaría muy agradecido 🙏
---
### Author:  [@Danny\_Dainton](https://meta.discourse.org/u/Danny_Dainton)
#### Post date: [19 Enero, 2024 17:27 UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195/3 "2024-01-19T17:27:20Z")
Pude hacer que algo funcionara, con algo de ayuda de IA, que se adapta a mi propio contexto (es posible que necesites ajustar la referencia a `solved`). 🏆
Comparto las soluciones finales por si a otros les resulta útil. 🙏
**La primera iteración:**
```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN tf.name = 'accepted_solution_id' THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN topic_custom_fields tf ON t.id = tf.topic_id AND tf.name = 'accepted_solution_id'
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
```
**La solución final para mi caso de uso específico:**
```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-19
SELECT
c.id AS category_id,
COUNT(DISTINCT t.id) AS topics,
COUNT(p.id) AS posts,
SUM(p.like_count) AS likes,
SUM(p.reads) AS reads,
COUNT(DISTINCT CASE WHEN ua.action_type = 15 THEN t.id END) AS solutions,
COUNT(CASE WHEN p.reply_to_post_number IS NOT NULL THEN p.id END) AS replies
FROM categories c
INNER JOIN topics t ON t.category_id = c.id
INNER JOIN posts p ON p.topic_id = t.id AND p.post_type = 1
LEFT JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.action_type = 15
WHERE p.created_at BETWEEN :start_date AND :end_date
GROUP BY c.id
ORDER BY COUNT(p.id) DESC
```
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [18 Febrero, 2024 17:28 UTC](https://meta.discourse.org/t/modify-a-report-to-include-the-replies-and-solved-metrics/292195/4 "2024-02-18T17:28:06Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.