Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help on group by with Join Expand / Collapse
Author
Message
Posted Sunday, February 3, 2013 7:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:29 PM
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...
Post #1415086
Posted Sunday, February 3, 2013 9:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
please provide ddl(table definitions ) and sample data ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1415096
Posted Sunday, February 3, 2013 9:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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
Post #1415098
Posted Sunday, February 3, 2013 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 4:29 PM
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.
Post #1415103
Posted Sunday, February 3, 2013 11:49 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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
Post #1415124
Posted Monday, February 4, 2013 12:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1415136
Posted Monday, February 4, 2013 7:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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 :D

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

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
Post #1415292
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse