May 2, 2015 at 10:39 pm
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here's my table definition and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[title] [nvarchar](500) NOT NULL,
[ogimg] [nvarchar](250) NULL,
[description] [nvarchar](500) NULL,
[amount] [tinyint] NOT NULL,
[price] [int] NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistryitems_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistryitems] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[giftregistryitems] WITH CHECK ADD CONSTRAINT [FK_giftregistryitems_giftregistry] FOREIGN KEY([registryid])
REFERENCES [dbo].[giftregistry] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistryitems] CHECK CONSTRAINT [FK_giftregistryitems_giftregistry]
GO
/****** Object: Table [dbo].[giftregistrypurchases] Script Date: 02-05-15 22:37:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistrypurchases](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[itemid] [int] NOT NULL,
[emailid] [int] NOT NULL,
[amount] [tinyint] NOT NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistrypurchases_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistrypurchases] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[giftregistrypurchases] WITH CHECK ADD CONSTRAINT [FK_giftregistrypurchases_giftregistry] FOREIGN KEY([registryid])
REFERENCES [dbo].[giftregistry] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistrypurchases] CHECK CONSTRAINT [FK_giftregistrypurchases_giftregistry]
GO
SET IDENTITY_INSERT [dbo].[giftregistryitems] ON
GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], , [amount], [price], [createdate]) VALUES (4, 2, N'coffee cups', N'', N'some coffee cups', N'www.coffee.com', 12, 10, CAST(N'2015-05-02 01:02:24.053' AS DateTime))
GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], , [amount], [price], [createdate]) VALUES (5, 2, N'Microsoft Surface Pro 3 - 12" Tablet - 256GB SSD, Intel Core i7 Haswell, 8GB RAM', N'http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg', N'US $1,149.99 Manufacturer refurbished in Computers/Tablets & Networking, iPads, Tablets & eBook Readers', N'http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697', 1, 1010, CAST(N'2015-05-02 21:27:02.363' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistryitems] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistrypurchases] ON
GO
INSERT [dbo].[giftregistrypurchases] ([id], [registryid], [itemid], [emailid], [amount], [createdate]) VALUES (1, 2, 4, 1, 3, CAST(N'2015-05-02 22:21:41.640' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistrypurchases] OFF
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply