All geek questions in one place

SQL Server Count on grouped by

I have 3 tables. I want to group by item.name element and count the time that can be found in idetail. This query only considers all rows that are not grouped by row.

Does anyone know how to fix this?

SELECT i.name, COUNT(i.name) FROM item AS i INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id WHERE ic.ic_id = 1002 GROUP BY i.name
+4
source share
2 answers

Is this what you want:

 select x.name , count(*) as cntNames , sum(x.cntDetails) as cntDetails from ( SELECT i.name, COUNT(*) as cntDetails FROM item AS i INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id WHERE ic.ic_id = 1002 -- NOTICE THE 2nd Value in the group by! GROUP BY i.name,id.id_category_id ) x group by name
+1
source 
 Select Count(*) From ( Select i.name, Count(i.name) From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name ) As Z

If you need all three columns and you are using SQL Server 2005 +

 With Items As ( Select I.name, Count(i.name) As ItemCount From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name ) Select Name, ItemCount , ( Select Count(*) From Items ) As OverallCount From Items

Another simpler version if you are using SQL Server 2005+.

 Select i.name , Count(i.name) As CountByName , Count(*) Over() As TotalCount From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name
+1
source

Source: https://habr.com/ru/post/1335656/

More articles:


All Articles