Task 35:

Product Colors in Every Category

SQL Server 2022

For every top-level product category (a category without a parent) and every product color, count the products of that color in the category's subcategories. Products without a color are not counted; a category–color pair without products must be shown with 0.
Output columns:
- category - the top-level category name
- color
- products - the number of products

Sort the result by category, then by color.

Expected result Columns: 3 · Rows: 36

category	color	products

Sample rows open after wrong checks: 0 of 2

Write your query below and click "Check it!". Tables are described in the right pane.

Tutorial

The query result will appear here. Ctrl+Enter - Run query

AdventureWorks Database: table structure and schema overview

The AdventureWorks database (SQL Server) is a sample dataset that models business processes of a fictional manufacturing company.

This page presents table structure, key columns, and relationships used for practical SQL learning and query practice.

The AdventureWorks database contains 10 main tables.

ER diagram of the AdventureWorks database showing table relationships AdventureWorks DB ER diagram

More about the AdventureWorks database: schema, sample queries and all exercises →

List of tables

Address - table of addresses.

AddressIDunique identifier for each address (PK)
AddressLine1the first line of the address
AddressLine2the second line of the address
Citycity
StateProvincestate or province
CountryRegioncountry
PostalCodepostal code
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (AddressID)

AddressID	AddressLine1	AddressLine2	City	StateProvince	CountryRegion	PostalCode	rowguid	ModifiedDate
9	8713 Yosemite Ct.	null	Bothell	Washington	United States	98011	268AF621-76D7-4C78-9441-144FD139821A	2006-07-01 00:00:00.000

Customer - table of customers.

CustomerIDunique identifier for each customer (PK)
NameStyle0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. Default: 0
Titletitle
FirstNamename
MiddleNamemiddle name
LastNamelast name
Suffixsuffix
CompanyNamecompany name
SalesPersonSalesPerson
EmailAddressE-mail
Phonephone number
PasswordHashpassword hash
PasswordSaltsalt
rowguidrowguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (CustomerID)

CustomerID	NameStyle	Title	FirstName	MiddleName	LastName	Suffix	CompanyName	SalesPerson	EmailAddress	Phone	PasswordHash	PasswordSalt	rowguid	ModifiedDate
1	0	Mr.	Orlando	N.	Gee	[null]	A Bike Store	adventure-works\pamela0	orlando0@adventure-works.com	245-555-0173	L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=	1KjXYs4=	3F5AE95E-B87D-4AED-95B4-C3797AFCB74F	2005-08-01 00:00:00.000

CustomerAddress - customer to address relations.

CustomerIDidentifier of client in the Customer table
AddressIDidentifier of address in the Address table
AddressTypeaddress type
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (CustomerID, AddressID)

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
FOREIGN KEY (AddressID) REFERENCES Address(AddressID)

CustomerID	AddressID	AddressType	rowguid	ModifiedDate
29485	1086	Main Office	16765338-DBE4-4421-B5E9-3836B9278E63	2007-09-01 00:00:00.000

Product - table of products.

ProductIDunique identifier for each product (PK)
Nameproduct name
ProductNumberarticle number
Colorproduct color
StandardCostproduct price
ListPriceproduct price in the catalogue
Sizeproduct size
Weightproduct weight
ProductCategoryIDforeign key pointing to ProductCategory table
ProductModelIDforeign key pointing to ProductModel table
SellStartDatetimestamp of the sales start date
SellEndDatetimestamp of the sales end date
DiscontinuedDatetimestamp of the sales end date
ThumbNailPhotothumbnail photo of the product
ThumbnailPhotoFileName
name of the photo thumbnail file
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (ProductID, ProductCategoryID, ProductModelID)

FOREIGN KEY (ProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)

ProductID	Name	ProductNumber	Color	StandardCost	ListPrice	Size	Weight	ProductCategoryID	ProductModelID	SellStartDate	SellEndDate	DiscontinuedDate	ThumbNailPhoto	ThumbnailPhotoFileName	rowguid	ModifiedDate
680	HL Road Frame - Black, 58	FR-R92B-58	Black	1059.3100	1431.5000	58	1016.04	18	6	2002-06-01 00:00:00.000	[null]	[null]	[binary]	no_image_available_small.gif	43DD68D6-14A4-461F-9069-55309D90EA7E	2008-03-11 10:01:36.827

ProductCategory - table of product categories.

ProductCategoryIDunique identifier for each product category (PK)
ParentProductCategoryIDID of the parent product category
Namename of the product category
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (ProductCategoryID)

FOREIGN KEY (ParentProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)

ProductCategoryID	ParentProductCategoryID	Name	rowguid	ModifiedDate
1	[null]	Bikes	CFBDA25C-DF71-47A7-B81B-64EE161AA37C	2002-06-01 00:00:00.000

ProductDescription - table of product descriptions.

ProductDescriptionIDunique ID for record (PK)
Descriptionproduct description
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (ProductDescriptionID)

ProductDescriptionID	Description	rowguid	ModifiedDate
4	Aluminum alloy cups; large diameter spindle.	DFEBA528-DA11-4650-9D86-CAFDA7294EB0	2007-06-01 00:00:00.000

ProductModel - table of product models.

ProductModelIDunique ID for each record (PK)
Namename of the product model
CatalogDescriptiondescription in XML format
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (ProductModelID)

ProductModelID	Name	CatalogDescription	rowguid	ModifiedDate
1	Classic Vest	[null]	29321D47-1E4C-4AAC-887C-19634328C25E	2007-06-01 00:00:00.000

ProductModelProductDescription - table of product models descriptions.

ProductModelIDID of client in the ProductModel table
ProductDescriptionIDID of address in the ProductDescription table
Culturelanguage code in ISO format
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (ProductModelID, ProductDescriptionID)

FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)
FOREIGN KEY (ProductDescriptionID) REFERENCES ProductDescription(ProductDescriptionID)

ProductModelID	ProductDescriptionID	Culture	rowguid	ModifiedDate
1	1199	en	4D00B649-027A-4F99-A380-F22A46EC8638	2007-06-01 00:00:00.000

SalesOrderDetail - table of sales orders details.

SalesOrderIDforeign key referencing the SalesOrderHeader table
SalesOrderDetailIDunique identifier of record in the table
OrderQtyquantity
ProductIDa foreign key referencing the Product table
UnitPriceprice per unit of goods
UnitPriceDiscountprice per unit of product with a discount
LineTotaltotal amount by line
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (SalesOrderID, SalesOrderDetailID, ProductID)

FOREIGN KEY (SalesOrderID) REFERENCES SalesOrderHeader(SalesOrderID)
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

SalesOrderID	SalesOrderDetailID	OrderQty	ProductID	UnitPrice	UnitPriceDiscount	LineTotal	rowguid	ModifiedDate
71774	110562	1	836	356.8980	.0000	356.898000	E3A1994C-7A68-4CE8-96A3-77FDD3BBD730	2008-06-01 00:00:00.000

SalesOrderHeader - product sales orders.

SalesOrderIDunique identifier of record in the table (PK)
RevisionNumberrevision number
OrderDatetimestamp for creating the order date
DueDatetimestamp of the order payment date
ShipDatetimestamp of the date the order was shipped
Statusorder status
OnlineOrderFlagonline order (yes/no)
SalesOrderNumberorder number
PurchaseOrderNumberpurchase number
AccountNumberaccount number
CustomerIDforeign key referencing the Customer table
ShipToAddressIDforeign key referencing the Address table defines the delivery address
BillToAddressIDforeign key referencing the Address table defines the account address
ShipMethoddelivery method
CreditCardApprovalCode
credit card confirmation code
SubTotalsubtotal
TaxAmttaxes
Freightdelivery cost
TotalDuetotal
Commentcomment
rowguidguid
ModifiedDatetimestamp of row creation or last update

PRIMARY KEY, btree (SalesOrderID, CustomerID, ShipToAddressID, BillToAddressID)

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
FOREIGN KEY (ShipToAddressID) REFERENCES Address(AddressID)
FOREIGN KEY (BillToAddressID) REFERENCES Address(AddressID)

SalesOrderID	RevisionNumber	OrderDate	DueDate	ShipDate	Status	OnlineOrderFlag	SalesOrderNumber	PurchaseOrderNumber	AccountNumber	CustomerID	ShipToAddressID	BillToAddressID	ShipMethod	CreditCardApprovalCode	SubTotal	TaxAmt	Freight	TotalDue	Comment	rowguid	ModifiedDate
71774	2	2008-06-01 00:00:00.000	2008-06-13 00:00:00.000	2008-06-08 00:00:00.000	5	0	SO71774	PO348186287	10-4020-000609	29847	1092	1092	CARGO TRANSPORT 5	[null]	880.3484	70.4279	22.0087	972.7850	[null]	89E42CDC-8506-48A2-B89B-EB3E64E3554E	2008-06-08 00:00:00.000

Practice

Lessons

Playground

Test

Product Colors in Every Category

Login

Register

Product Colors in Every Category