Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help on group by with Join


help on group by with Join

Author
Message
pawan_rb
pawan_rb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
I have two tables. One has records for transactions, another has records for purchased products. Transaction table has TransactionID, TotalAmount, TransactionDate. The purchasedProducts table has ProductID and TransactionID. Each transaction can have multiple products that are purchased. Products table has Product ID, Product Name and Price.
Now, how do I get a report so that I get transaction details in following format:

Transaction ID-------TransactionDate---ProductName------TotalAmount
1-------------------- 01/18/2012-------Product A---------500
---------------------------------------Product B
----------------------------------------Product C
2--------------------01/25/2012--------Product X---------800
3--------------------01/30/2012--------Product Y---------1000
----------------------------------------Product Z

And so on...
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1192
please provide ddl(table definitions ) and sample data ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1192
pawan_rb (2/3/2013)
I have two tables. One has records for transactions, another has records for purchased products. Transaction table has TransactionID, TotalAmount, TransactionDate. The purchasedProducts table has ProductID and TransactionID. Each transaction can have multiple products that are purchased. Products table has Product ID, Product Name and Price.
Now, how do I get a report so that I get transaction details in following format:

Transaction ID-------TransactionDate---ProductName------TotalAmount
1-------------------- 01/18/2012-------Product A---------500
---------------------------------------Product B
----------------------------------------Product C
2--------------------01/25/2012--------Product X---------800
3--------------------01/30/2012--------Product Y---------1000
----------------------------------------Product Z

And so on...

If you are working on SSRS reporting ; then create a simple inner join for the dataset , then design Grouping sets for the columns ...I guess what you are looking for in this one would be Drill Down reports .

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
pawan_rb
pawan_rb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
Thanks. Can you please also show me thae way to compose the sql? I was also thinking in the same line like in er joining and grouping but couldnt make it to work... Thank you.
demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1192
pawan_rb (2/3/2013)
Thanks. Can you please also show me thae way to compose the sql? I was also thinking in the same line like in er joining and grouping but couldnt make it to work... Thank you.


could you please provide the table definitons and sample ?
http://www.sqlservercentral.com/articles/Best+Practices/61537

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
demonfox (2/3/2013)
pawan_rb (2/3/2013)
Thanks. Can you please also show me thae way to compose the sql? I was also thinking in the same line like in er joining and grouping but couldnt make it to work... Thank you.


could you please provide the table definitons and sample ?
http://www.sqlservercentral.com/articles/Best+Practices/61537


+1

This is what I have come up with based on your requirement. Not sure for what you need a group by when total amount in there in Transaction table.

CREATE TABLE [dbo].[PurchasedProducts](
[ProductID] [int] NULL,
[TransactionID] [int] NULL
)
GO


CREATE TABLE [dbo].[Products](
[ProductID] [int] NULL,
[ProductName] [varchar](100) NULL,
[Price] [int] NULL
)
GO

CREATE TABLE [dbo].[TransactionTbl](
[TransactionID] [int] NULL,
[TotalAmount] [int] NULL,
[TransactionDate] [date] NULL
)
GO

INSERT INTO Products
VALUES
(1,'Product_A',100),
(2,'Product_B',200),
(3,'Product_C',300),
(4,'Product_D',400),
(5,'Product_E',500);

INSERT INTO TransactionTbl
VALUES
(1001,1000,GETDATE()-2),
(1002,900,GETDATE()-1),
(1003,800,GETDATE())

INSERT INTO PurchasedProducts
VALUES
(1,1001),(2,1001),(3,1001),(4,1001),
(4,1002),(5,1002),
(3,1003),(5,1003)

SELECT tt.TransactionID,tt.TransactionDate,p.ProductName,tt.TotalAmount
FROM TransactionTbl tt
INNER JOIN PurchasedProducts pp on tt.TransactionID = pp.TransactionID
INNER JOIN Products p on p.ProductID = pp.ProductID



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


demonfox
demonfox
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1192
Lokesh Vij (2/4/2013)


This is what I have come up with based on your requirement. Not sure for what you need a group by when total amount in there in Transaction table.


Exactly ; that is why , the DDL and Sample data BigGrin

was suddenly got busy ; realised again, that Data error would always come , when you least expect it BigGrin

Pawan , Please post the exact requirement , because what I understood is that , you are expecting the product cost sum at the end,and that is why you were looking for Group by ; but, you stated in your question , you already have the sum amount, so , it shouldn't require group by, an inner join should be suffice.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search