Need help in fetching total shares of a customer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help in fetching total shares of a customer

Author

Topic

fusionplus
Starting Member

1 Post

Posted - 2011-10-10 : 17:22:43

Hi,

I have 3 tables customer(cid, name, phone) and transactions (cid (reference), fundid, date, shares) and fund (fundid, fund_name).

I am trying to write an sql query that would get me the total number of shares for each customer for each fund.

Here are the sample inserts:


INSERT INTO CUSTOMER(1, 'Alex', '123456678');
INSERT INTO CUSTOMER(2, 'Bill', '6323450236');
INSERT INTO CUSTOMER(3, 'Marie', '8568289912');

INSERT INTO FUND (1, 'Docotel');
INSERT INTO FUND (2, 'Armen');
INSERT INTO FUND (3, 'TD');

INSERT INTO TRANSACTIONS(1, 2, '2010-2-12', 234); (means shares bought)
INSERT INTO TRANSACTIONS(3, 1, '2010-4-2', 192);
INSERT INTO TRANSACTIONS(1, 2, '2010-4-22', -45); (the '-' means shares sold) 
INSERT INTO TRANSACTIONS(1, 3, '2010-4-26', 220);
INSERT INTO TRANSACTIONS(3, 2, '2010-7-21', 170);

I want the sql result to look something like this:

Name| Fund_Name | Total_Shares |

Alex Docotel 189
Alex TD 220
Marie Docotel 192
Marie Armen 170

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-10-10 : 19:19:38

SELECT c.[name],f.fund_name,[shares] = sum(t.shares)
FROM customer c
INNER JOIN transactions t ON c.cid = t.cid
LEFT JOIN Fund f ON t.fundid = f.fundid
GROUP BY c.[name],f.fund_name

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:10:03

if you want to list even customers with zero shares you need a slight tweak


SELECT m.cust_name,m.fund_name,
[shares] = sum(t.shares)
FROM
(
SELECT c.cid,c.[name] AS cust_name,f.fundid,f.fund_name
FROM customer c
CROSS JOIN Fund f
)m
LEFT JOIN transactions t ON c.cid = m.cid
AND t.fundid = m.fundid
GROUP BY m.[cust_name],m.fund_name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:14:30

quote:
Originally posted by jimf

SELECT c.[name],f.fund_name,[shares] = sum(t.shares)
FROM customer c
INNER JOIN transactions t ON c.cid = t.cid
LEFT JOIN Fund f ON t.fundid = f.fundid
GROUP BY c.[name],f.fund_name

Jim

Everyday I learn something that somebody else already knew

Didnt get need of last LEFT JOIN. I feel it can be INNER JOIN as customer having a transaction will be having a fund associated to it
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources