Skip to content
Advertisement

Is there a way to have a distinct clause and SUM clause at the same time?

I have this table in my database, which stores the data of requests made by a tool application.

select * from details

IdDetail | Folio | IdTool | Cost |
1        |53     |917     |137   |
2        |53     |918     |145   |
3        |53     |919     |15    |
4        |54     |917     |137   |
5        |54     |917     |137   |
6        |54     |916     |56    |

It stores the information of the request(Folio), of the tools(IdHerramienta and Gasto[Cost]). My objective is to get an statement to obtain the data of the details, only showing distinct IdHerramienta(IdTool) per row, how many of them are with the same Folio, and the sum of the cost in case the IdTool gets repeated (if a tool appears multiple times with the same Folio, add its cost for each time it gets repeated)

So far I can only show how many times a tool gets repeated, but I want to get the cost too, any ideas about how to get it?

select distinct IdTool, count(IdTool) as Quantity 
from details where Folio = 54 group by IdTool

| IdTool | Quantity |
|917     |2         |
|916     |1         |

Results I want to obtain

Folio | IdTool | Quantity | Cost  |
54    |917     |2         |274    |
54    |916     |1         |56     |

Advertisement

Answer

You seem to be looking for aggregation:

select folio, id_tool, count(*) quantity, sum(cost) cost
from mytable
where folio = 56
group by folio, id_tool
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement