Question

Imagine a Book table that had the following: Book Table BookId Category Price Discount Publisher Date Next, let's say you had to group by Publisher and Category while retrieving the total price and total number of rows within that grouping.

1. Write the SQL to figure out how many types of Publisher values and Category values there are.

2. How would you write an equation to demonstrate the number of groupings you would expect if you knew the exact number of types of Publisher and Category fields? For example, if you knew there were 3 types of Publisher and 4 types of Category, how many groupings would you expect?

Answer 1

1.SELECT COUNT(Publisher) AS No_of_Publisher, (SELECT COUNT(Category) FROM Book GROUP BY Category) AS No_Of_category

FROM Book GROUP BY Publisher;

EXPLANATION:-

Here, SELECT is used to fetch the data from the table.

COUNT() is used to calculate the rows from the table.

We are getting count of publisher in main query and getting count of category in the sub query .

Grouping is done to calculate the number of distinct category and Publishers.

2. SELECT COUNT(Distinct_Row ) AS No_Of_Grouping FROM (

SELECT Distinct Publisher,Category,"Row" as Distinct_Row FROM

Book ) ;

EXPLANATION:-

Here , we are getting combined grouping of category and Publisher.

In sub query we fetch the distinct pairs of publisher and category from the Book table .

In SELECT we add one more column with Row as input.

In main query we calculate the number of distinct publisher and category pair using the Distinct pairs .

This gives us number of groupings currently we have.