Get rows and sum in joined table
-
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, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:
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:
USE [tt]
GO
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,
[listuuid] [nvarchar](50) NOT NULL CONSTRAINT [DF_giftregistry_listuuid] DEFAULT (newid()),
[userid] [nvarchar](50) NOT NULL,
[title] [nvarchar](50) NOT NULL,
[description] [nvarchar](500) NULL,
[invitetext] [nvarchar](1000) NULL,
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistry_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistry] 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
/****** Object: Table [dbo].[giftregistryemails] Script Date: 09-05-15 11:15:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryemails](
[id] [int] IDENTITY(1,1) NOT NULL,
[registryid] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[nvarchar](150) NOT NULL,
[pwd] [nvarchar](10) NULL,
[sentdate] [datetime] NULL CONSTRAINT [DF_giftregistryemails_sentdate] DEFAULT (((1)/(1))/(1900)),
[createdate] [datetime] NOT NULL CONSTRAINT [DF_giftregistryemails_createdate] DEFAULT (getdate()),
CONSTRAINT [PK_giftregistryemails] 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
/****** Object: Table [dbo].[giftregistryitems] Script Date: 09-05-15 11:15:18 ******/
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,
[trackingURL] [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
/****** Object: Table [dbo].[giftregistrypurchases] Script Date: 09-05-15 11:15:18 ******/
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 [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
SET IDENTITY_INSERT [dbo].[giftregistry] ON
GO
INSERT [dbo].[giftregistry] ([id], [listuuid], [userid], [title], [description], [invitetext], [createdate]) VALUES (2, N'83875b62-3cc1-4516-b932-6e60a116cbff', N'32DD30EB-1691-457B-9FF5-FC41D687E579', N'My list', N'We''re getting married', N'Hey you guys coming too?', CAST(N'2015-04-29 21:21:53.813' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistry] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistryemails] ON
GO
INSERT [dbo].[giftregistryemails] ([id], [registryid], [name], , [pwd], [sentdate], [createdate]) VALUES (2, 2, N'Peter', N'peter@hotmail.com', N'1234', CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'2015-05-02 22:01:53.783' AS DateTime))
GO
INSERT [dbo].[giftregistryemails] ([id], [registryid], [name], , [pwd], [sentdate], [createdate]) VALUES (3, 2, N'Eric', N'eric@outlook.com', N'1234', CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'2015-05-02 22:01:53.783' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistryemails] OFF
GO
SET IDENTITY_INSERT [dbo].[giftregistryitems] ON
GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], , [trackingURL], [amount], [price], [createdate]) VALUES (9, 2, N'1111', N'http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg', N'Surface Pro 3', N'http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697', NULL, 1, 114998, CAST(N'2015-05-05 20:06:57.143' AS DateTime))
GO
INSERT [dbo].[giftregistryitems] ([id], [registryid], [title], [ogimg], [description], , [trackingURL], [amount], [price], [createdate]) VALUES (10, 2, N'Coffee cups', N'http://www.trouwen-feestartikelen.nl/images/rozenblaadjes-burgundy.jpg', N'Great cups', N'https://www.coffee.com/cups', NULL, 6, 25, CAST(N'2015-05-05 20:57:05.900' 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 (3, 2, 9, 2, 5, CAST(N'2015-05-09 11:18:12.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistrypurchases] OFF
GO
ALTER TABLE [dbo].[giftregistrypurchases] ADD CONSTRAINT [DF_giftregistrypurchases_createdate] DEFAULT (getdate()) FOR [createdate]
GO
ALTER TABLE [dbo].[giftregistryemails] WITH CHECK ADD CONSTRAINT [FK_giftregistryemails_giftregistry] FOREIGN KEY([registryid])
REFERENCES [dbo].[giftregistry] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistryemails] CHECK CONSTRAINT [FK_giftregistryemails_giftregistry]
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
ALTER TABLE [dbo].[giftregistrypurchases] WITH CHECK ADD CONSTRAINT [FK_giftregistrypurchases_giftregistryitems] FOREIGN KEY([itemid])
REFERENCES [dbo].[giftregistryitems] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[giftregistrypurchases] CHECK CONSTRAINT [FK_giftregistrypurchases_giftregistryitems]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the unique code for this list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistry', @level2type=N'COLUMN',@level2name=N'listuuid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the generated password for this list and this user' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistryemails', @level2type=N'COLUMN',@level2name=N'pwd'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The user pasted URL is converted to a tracking URL where possible' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'giftregistryitems', @level2type=N'COLUMN',@level2name=N'trackingURL'
GO
Desired results:
purchasedamountidregistryidtitleogimgdescriptionURLamountpricecreatedate
542Coffee cupshttp://www.trouwen-feestartikelen.nl/images/rozenblaadjes-burgundy.jpgGreat cupshttps://www.coffee.com/cups62557:05.9
052Surface Pro 3http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpgSurface Pro 3http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697111499806:57.1
-
Quick thought, first of all, I think there is an error in the code as purchase registryid does not match the item id.
😎
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
Should be
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.registryid
WHERE gi.registryid=2
The query can be changed to return both the total and the item price per item line by adding the OVER clause to the SUM
SELECTSUM(gi.amount) OVER (PARTITION BY gi.registryid) AS TOTAL_AMOUNT
,SUM(gi.amount) OVER (PARTITION BY gi.id) AS ITEM_AMOUNT
,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.registryid
WHERE gi.registryid=2
Results
TOTAL_AMOUNT ITEM_AMOUNT id registryid title ogimg description URL amount price createdate id registryid itemid emailid amount createdate------------ ----------- ----------- ----------- ---------------------------------------------------------------------------------- ------------------------------------------------------------- --------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- ------ ----------- ----------------------- ----------- ----------- ----------- ----------- ------ -----------------------
13 12 4 2 coffee cups some coffee cups http://www.coffee.com 12 10 2015-05-02 01:02:24.053 1 2 4 1 3 2015-05-02 22:21:41.640
13 1 5 2 Microsoft Surface Pro 3 - 12" Tablet - 256GB SSD, Intel Core i7 Haswell, 8GB RAM http://i.ebayimg.com/images/i/281656969697-0-1/s-l1000.jpg US $1,149.99 Manufacturer refurbished in Computers/Tablets & Networking, iPads, Tablets & eBook Readers http://www.ebay.com/itm/Microsoft-Surface-Pro-3-12-Tablet-256GB-SSD-Intel-Core-i7-Haswell-8GB-RAM-/281656969697 1 1010 2015-05-02 21:27:02.363 1 2 4 1 3 2015-05-02 22:21:41.640
-
Thanks!
I forgot to add a critical component I see now, the data for table giftregistrypurchases.
I updated my question with that data and desired results.
btw: How do you get your samples results table formatted so nicely aligned/readable?
-
petervdkerk (5/3/2015)
Thanks!I forgot to add a critical component I see now, the data for table giftregistrypurchases.
I updated my question with that data and desired results.
btw: How do you get your samples results table formatted so nicely aligned/readable?
No worries, glad to help.
😎
To format the output, in SSMS press CTRL + T for a text output, copy the results and paste into the replay, then select it and click the
link on the left hand side. -
Ok, not sure what you mean by pasting in into replay...it looks better now anyway.
However, my actual question is not answered yet, as I still don't have the desired results as displayed in my original post...could you update your query to give the desired results?
Thanks again!
-
petervdkerk (5/3/2015)
Ok, not sure what you mean by pasting in into replay...it looks better now anyway.However, my actual question is not answered yet, as I still don't have the desired results as displayed in my original post...could you update your query to give the desired results?
Thanks again!
By pasting into the reply, I mean the the content of the post, sorry I wasn't very clear on this.
😎
-
Ah, got it now 🙂
And could you have a look at my updated question?
-
petervdkerk (5/3/2015)
Ah, got it now 🙂And could you have a look at my updated question?
Can you add the DDL and data for the dbo.giftregistry table, it is missing from your code?
😎
-
Done! 🙂 Thanks again
-
Any idea already? 🙂
-
petervdkerk (5/5/2015)
Any idea already? 🙂Still cannot run the DDL and sample insert as the insert for the dbo.giftregistry is missing;-)
😎
-
Whoops! Here you go! You good now? 🙂
SET IDENTITY_INSERT [dbo].[giftregistry] ONGO
INSERT [dbo].[giftregistry] ([id], [listuuid], [userid], [title], [description], [invitetext], [createdate]) VALUES (2, N'83875b62-3cc1-4516-b932-6e60a116cbff', N'32DD30EB-1691-457B-9FF5-FC41D687E579', N'my list', N'my descr', N'hey u there?!?', CAST(N'2015-04-29 21:21:53.813' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[giftregistry] OFF
GO
-
First of all: thank you for your extensive explanation, awesome! 🙂
A lot of feedback, which to be honest is going to require a lot of redesign of my DB, as your feedback not only applies to these tables but to a lot of others as well.
Now, back to your feedback and my questions on that:
We use double quotes for data element names; the single quote is an old Sybase dialect and the square brackets are an old MS dialect. There is no such crap as a generic “amount” in RDBMS; it has to be “<something in particular>_amount” to follow the ISO-11179 rules. You did this with “purchase_amount”.
Where do I do this "single quoting" you refer to spefically? A lot of the scripting for the DDL was generated by SQL Server's generate script command, so nothing I came up with on my own.
Finally, carrying a total in a table is a design flaw called a non-normal form redundancy.
The total amount I want to return with this table is basically a sum of a calculation. I though that I should never store values in a table if those values can already be derived from other tables, in my case a sum on a different table. I want to know for each row, how often that item occurs in a different table, so instead of performing 2 database requests I want to have the amount already there. How is my thinking flawed?
Did you know that the U in UUID stands for “universal” and not for local? That is why it is only used to get things that are external to the schema. But you used it locally. This is fundamentally wrong, as well as waste of space.
Yes, I knew that. However, I used uuid here, because my visitors can call a giftregistry item via the URL and I don't want easily readable sequential numbers as it becomes very easy to guess those numbers. That's the only reason I used a uuid there.
I see that you have the magical NVARCHAR(50) all over the tables. Why do you need a strign of Chinese characters that are far too long for the data elemetns they model? This is a left-over from ACCESS and some other old Microsoft products; it was done to play sage by defaultign to an insanely large size and the most general data type.
This is new to me. I thought an nvarchar(50) for example was the most efficient datatype to reserve space for a string from NULL or 0 to 50 characters. But I see you use char(50). What is the difference in performance/space?
Do automobiles change into squids?
Last time I checked, no 😉
You used “creation_date” which I guess applies to the row, not to the item. Think about it; you can only know when an item was registered, not when it was created.
It applies to when the user created this item. I want to be able to see when someone created this item. That's the only purpose here. I'm not sure I understand your feedback on this one.
Your “SELECT (SELECT SUM(..) FROM..)” construct is not wrong but it is very expensive. The use of “EXEC sys.sp_addextendedproperty” is expensive and destroys portability. Do not nest queries this way.
This was generated by SQL Server's "generate script" function in SQL Server management studio. So only used upon creation of the tables, and only for this forum post now.
client_id CHAR(10) NOT NULL, -- why did you say user?
Because actually my userid is a 50 character string...something that has been there for years...so need to work with that until a redesign.
Can you do it, or you do still need help?
Once again, thank you very much for your feedback, I think I need to seriously get back to the drawing board.
However, I'm under pressure, so need to work with what I have. Before a redesign (which I think is needed now?), is it easy for you to help out with a query that runs on my existing definitions?
Thank you once again!
-
Quick questions:
😎
1) The amount column in giftregistrypurchase table, is that the quantity of items?
2) In the same table, is the itemid the giftregistryitems.id?
Looking at the schema, I suspect that either there is something missing or the design is flawed.
+-----------------+ +-----------------------+| giftregistry | | giftregistrypurchases |
+-----------------+ +-----------------------+
|(pk) id |-(1)----, |(pk) id |
| listuuid |-(1)--, '(0,n)-|(fk) registryid |
| userid | | | itemid |
| title | | | emailid |
| description | | | amount |
| invitetext | | | createdate |
| createdate | | +-----------------------+
+-----------------+ |
|
| +--------------------+
| | giftregistryitems |
| +--------------------+
| |(pk) id |
',-(0,n)-|(fk) registryid |
| title |
| ogimg |
| description |
| URL |
| amount |
| price |
| createdate |
+--------------------+
My suspicion is that this (below) is more like the intention.
+--------------+ +----------------------+ +-------------------+| giftregistry |-+-----<| giftregistrypurchase |>-----+-| giftregistryitems |
| (the buyer) | | (the sale) | | (the goods) |
+--------------+ +----------------------+ +-------------------+
One entry for each One entry for each purchase One entry for each specific
user/buyer. of an item found in the item available, the quantity
items table, the number of available, the price of each
items bought in that item and the date of entry.
purchase and the date and
time of the purchase.
-
A little explanation:
[giftregistry] is the general description of the list, the bridal couple in this is the only owner. They can add a text they want to send to the guests.
[giftregistryitems] contains the items the bridal couple is requesting. Here column amount is the total amount they want of that item, e.g. 6 coffee cups
[giftregistryemails] contains the guests
[giftregistrypurchases] contains which guest has purchased which amount of which item, e.g. guest nr. 3 has purchased 2 coffee cups (of the total 6 requested)
1) The amount column in giftregistrypurchase table, is that the quantity of items?
Yes, the amount of requested items that was purchased
2) In the same table, is the itemid the giftregistryitems.id?
Yes, that's correct. I now noticed in the database diagram that this relationship is not defind...I'll update my original post with the latest DDL.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply