help on group by with Join

Question

help on group by with Join

pawan_rb

Grasshopper

Points: 10
More actions
February 3, 2013 at 7:24 pm

#269755

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...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

please provide ddl(table definitions ) and sample data ...

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

demonfox SSCertifiable Points: 6289 More actions · Answer 2

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 Grasshopper Points: 10 More actions · Answer 3

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 SSCertifiable Points: 6289 More actions · Answer 4

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 SSChampion Points: 10836 More actions · Answer 5

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%5B/quote%5D

+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[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 6

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 😀

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

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: