#Microsoft
Find the number of transactions that occurred for each product. Output the product name along with the corresponding number of transactions and order records by the product id in ascending order. You can ignore products without transactions.
table name: excel_sql_inventory_data
table name: excel_sql_transaction_data
Solution:
select i.product_name,count(d.transaction_id) as cnt
from excel_sql_inventory_data as i
join excel_sql_transaction_data
as d
on i.product_id = d.product_id
group by i.product_name,i.product_id
order by i.product_id
Output:
SQL Script:
USE [StrataScratch]
GO
CREATE TABLE [dbo].[excel_sql_inventory_data](
[product_id] [int] NULL,
[product_name] [varchar](50) NULL,
[product_type] [varchar](50) NULL,
[unit] [varchar](50) NULL,
[price_unit] [float] NULL,
[wholesale] [float] NULL,
[current_inventory] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[excel_sql_transaction_data](
[transaction_id] [int] NULL,
[time] [datetime] NULL,
[product_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (1, N'strawberry', N'produce', N'lb', 3.28, 1.77, 13)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (2, N'apple_fuji', N'produce', N'lb', 1.44, 0.43, 2)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (3, N'orange', N'produce', N'lb', 1.02, 0.37, 2)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (4, N'clementines', N'produce', N'lb', 1.19, 0.44, 44)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (5, N'blood_orange', N'produce', N'lb', 3.86, 1.66, 19)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (6, N'blood_lime', N'produce', N'lb', 1.69, 0.91, 10)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (7, N'tayberry', N'produce', N'lb', 2.54, 0.89, 25)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (8, N'pluot', N'produce', N'lb', 3.99, 1.44, 26)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (9, N'tangelo', N'produce', N'lb', 0.96, 0.56, 32)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (10, N'pomello', N'produce', N'lb', 0.96, 0.57, 33)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (11, N'pineberry', N'produce', N'lb', 4.89, 2, 42)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (12, N'vegan_egg_substitute', N'dairy', N'oz', 1.15, 0.58, 41)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (13, N'asparagus_water', N'beverages', N'fl_oz', 0.38, 0.21, 42)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (14, N'falafel_chips', N'snacks', N'oz', 0.53, 0.29, 2)
GO
INSERT [dbo].[excel_sql_inventory_data] ([product_id], [product_name], [product_type], [unit], [price_unit], [wholesale], [current_inventory]) VALUES (15, N'sweet_potato_hockey_pucks', N'snacks', N'oz', 0.43, 0.17, 35)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (1, CAST(N'2016-01-08T17:46:17.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (4, CAST(N'2016-01-06T17:57:42.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (8, CAST(N'2016-01-07T09:35:40.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (9, CAST(N'2016-01-03T09:06:20.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (9, CAST(N'2016-01-03T09:06:20.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (9, CAST(N'2016-01-03T09:06:20.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (11, CAST(N'2016-01-08T11:40:52.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (20, CAST(N'2016-01-04T10:01:39.000' AS DateTime), 6)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (20, CAST(N'2016-01-04T10:01:39.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (22, CAST(N'2016-01-06T14:30:08.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (24, CAST(N'2016-01-03T10:47:44.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (31, CAST(N'2016-01-05T13:19:25.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (32, CAST(N'2016-01-04T19:37:14.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (32, CAST(N'2016-01-04T19:37:14.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (35, CAST(N'2016-01-05T16:08:30.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (37, CAST(N'2016-01-06T15:31:36.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (40, CAST(N'2016-01-03T10:46:42.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (40, CAST(N'2016-01-03T10:46:42.000' AS DateTime), 1)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (43, CAST(N'2016-01-03T19:07:45.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (55, CAST(N'2016-01-05T19:13:42.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (57, CAST(N'2016-01-07T19:30:02.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (59, CAST(N'2016-01-08T14:39:42.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (75, CAST(N'2016-01-04T13:37:21.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (78, CAST(N'2016-01-06T09:03:31.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (78, CAST(N'2016-01-06T09:03:31.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (79, CAST(N'2016-01-05T19:43:36.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (79, CAST(N'2016-01-05T19:43:36.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (82, CAST(N'2016-01-04T18:07:08.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (86, CAST(N'2016-01-07T09:39:31.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (91, CAST(N'2016-01-07T12:17:27.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (92, CAST(N'2016-01-08T12:03:20.000' AS DateTime), 6)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (94, CAST(N'2016-01-04T10:43:37.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (95, CAST(N'2016-01-08T14:01:02.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (97, CAST(N'2016-01-08T12:26:21.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (99, CAST(N'2016-01-06T19:06:29.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (100, CAST(N'2016-01-05T16:32:42.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (101, CAST(N'2016-01-04T19:21:18.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (107, CAST(N'2016-01-08T17:51:34.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (112, CAST(N'2016-01-07T10:03:06.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (116, CAST(N'2016-01-05T11:28:48.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (117, CAST(N'2016-01-05T13:26:04.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (118, CAST(N'2016-01-06T14:27:33.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (120, CAST(N'2016-01-03T14:50:49.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (126, CAST(N'2016-01-03T12:37:39.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (128, CAST(N'2016-01-03T15:10:08.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (131, CAST(N'2016-01-06T19:20:17.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (135, CAST(N'2016-01-03T15:24:04.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (135, CAST(N'2016-01-03T15:24:04.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (136, CAST(N'2016-01-06T12:33:08.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (139, CAST(N'2016-01-03T19:44:07.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (148, CAST(N'2016-01-03T18:34:09.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (151, CAST(N'2016-01-06T17:49:39.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (151, CAST(N'2016-01-06T17:49:39.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (153, CAST(N'2016-01-06T08:57:52.000' AS DateTime), 1)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (155, CAST(N'2016-01-07T18:34:26.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (157, CAST(N'2016-01-04T14:54:18.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (158, CAST(N'2016-01-08T12:23:10.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (160, CAST(N'2016-01-04T19:26:45.000' AS DateTime), 1)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (163, CAST(N'2016-01-03T10:11:22.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (163, CAST(N'2016-01-03T10:11:22.000' AS DateTime), 6)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (164, CAST(N'2016-01-06T19:21:01.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (170, CAST(N'2016-01-04T16:24:04.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (170, CAST(N'2016-01-04T16:24:04.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (175, CAST(N'2016-01-03T11:58:03.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (182, CAST(N'2016-01-06T12:53:22.000' AS DateTime), 6)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (184, CAST(N'2016-01-08T10:19:18.000' AS DateTime), 6)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (185, CAST(N'2016-01-07T11:06:33.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (187, CAST(N'2016-01-08T11:50:14.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (188, CAST(N'2016-01-03T17:59:56.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (201, CAST(N'2016-01-03T08:12:07.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (203, CAST(N'2016-01-08T11:59:33.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (209, CAST(N'2016-01-06T19:33:19.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (210, CAST(N'2016-01-07T11:31:44.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (213, CAST(N'2016-01-04T08:06:28.000' AS DateTime), 4)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (217, CAST(N'2016-01-04T13:44:26.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (220, CAST(N'2016-01-03T16:03:57.000' AS DateTime), 14)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (220, CAST(N'2016-01-03T16:03:57.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (221, CAST(N'2016-01-04T14:29:52.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (224, CAST(N'2016-01-06T17:53:58.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (228, CAST(N'2016-01-06T09:52:54.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (229, CAST(N'2016-01-06T13:36:08.000' AS DateTime), 15)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (232, CAST(N'2016-01-06T16:53:45.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (233, CAST(N'2016-01-08T10:16:31.000' AS DateTime), 10)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (236, CAST(N'2016-01-04T15:47:23.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (239, CAST(N'2016-01-05T13:08:26.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (242, CAST(N'2016-01-04T11:31:55.000' AS DateTime), 1)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (243, CAST(N'2016-01-03T09:02:13.000' AS DateTime), 12)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (243, CAST(N'2016-01-03T09:02:13.000' AS DateTime), 1)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (243, CAST(N'2016-01-03T09:02:13.000' AS DateTime), 11)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (245, CAST(N'2016-01-06T08:55:32.000' AS DateTime), 9)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (246, CAST(N'2016-01-07T19:11:09.000' AS DateTime), 8)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (251, CAST(N'2016-01-04T08:01:26.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (253, CAST(N'2016-01-06T14:15:20.000' AS DateTime), 7)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (254, CAST(N'2016-01-04T13:55:59.000' AS DateTime), 3)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (258, CAST(N'2016-01-04T15:24:57.000' AS DateTime), 2)
GO
INSERT [dbo].[excel_sql_transaction_data] ([transaction_id], [time], [product_id]) VALUES (259, CAST(N'2016-01-08T18:09:29.000' AS DateTime), 12)
GO