none
count times each category exist RRS feed

  • Question

  • Hi every one

    As you can see in below picture.

    I have Category_ID,Parent_ID,Count

    Each Category is child of Parent .

    I search in file , then count times each category either child or parent exist in file.

    then i save it table CategoryCount.

    I need to get sum of child count + Parent count . this value will be set as sum of Parent.

    This is the way how it would do the sum.


    faheem

    Saturday, October 13, 2012 2:11 PM

Answers

  • Check out the following solution. NULLs indicate totaling.

    SELECT c.Name AS Category, sc.Name as Subcategory,
           Frequency = COUNT(*)
    FROM Production.Product p 
      INNER JOIN Production.ProductSubcategory sc
         ON p.ProductSubcategoryID=sc.ProductSubcategoryID
      INNER JOIN Production.ProductCategory c
         ON sc.ProductSubcategoryID = c.ProductCategoryID
    GROUP BY c.Name, sc.Name
    WITH CUBE
    ORDER BY Category, Subcategory;
    /* NULL	NULL	105
    NULL	Handlebars	8
    NULL	Mountain Bikes	32
    NULL	Road Bikes	43
    NULL	Touring Bikes	22
    Accessories	NULL	8
    Accessories	Handlebars	8
    Bikes	NULL	32
    Bikes	Mountain Bikes	32
    Clothing	NULL	22
    Clothing	Touring Bikes	22
    Components	NULL	43
    Components	Road Bikes	43  */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, October 13, 2012 2:46 PM
    Moderator

All replies

  • Have you seen the Dewey Decimal Classification system used in libraries? You are trying to fix a bad encoding with kludges in DML. Most of the work in RDBMS is in the DDL and the design of the data model. Would you use Roman numerals for engineering? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, October 13, 2012 2:38 PM
  • Check out the following solution. NULLs indicate totaling.

    SELECT c.Name AS Category, sc.Name as Subcategory,
           Frequency = COUNT(*)
    FROM Production.Product p 
      INNER JOIN Production.ProductSubcategory sc
         ON p.ProductSubcategoryID=sc.ProductSubcategoryID
      INNER JOIN Production.ProductCategory c
         ON sc.ProductSubcategoryID = c.ProductCategoryID
    GROUP BY c.Name, sc.Name
    WITH CUBE
    ORDER BY Category, Subcategory;
    /* NULL	NULL	105
    NULL	Handlebars	8
    NULL	Mountain Bikes	32
    NULL	Road Bikes	43
    NULL	Touring Bikes	22
    Accessories	NULL	8
    Accessories	Handlebars	8
    Bikes	NULL	32
    Bikes	Mountain Bikes	32
    Clothing	NULL	22
    Clothing	Touring Bikes	22
    Components	NULL	43
    Components	Road Bikes	43  */


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Saturday, October 13, 2012 2:46 PM
    Moderator