Select a sum with another table in SQL

Question

How to select the amount with another table if I have data as shown below:

Table element

MemberID Name DateJoin M0001 John 01/01/2015 M0002 Willy 03/20/2016 M0003 Teddy 02/01/2017 etc.... 

Table transaction

 MemberID TransDate Total M0002 02/01/2015 100000 M0002 02/28/2015 222000 M0001 01/01/2016 150000 M0001 01/26/2017 160000 M0002 01/25/2017 160000 M0003 02/01/2017 9000 

I want the result to be the sum of how many times a participant’s transaction in the store in 2015-2017

As a result, I want:

 MemberID 2015 2016 2017 M0001 0 1 1 M0002 2 0 1 M0003 0 0 1 

How many members will appear in the results, although it does not have a transaction.

Answer 1

 CREATE TABLE #Table1 ([MemberID] varchar(5), [Name] varchar(5), [DateJoin] datetime) ; INSERT INTO #Table1 ([MemberID], [Name], [DateJoin]) VALUES ('M0001', 'John', '2015-01-01 00:00:00'), ('M0002', 'Willy', '2016-03-20 00:00:00'), ('M0003', 'Teddy', '2017-02-01 00:00:00') ; CREATE TABLE #Table2 ([MemberID] varchar(5), [TransDate] datetime, [Total] int) ; INSERT INTO #Table2 ([MemberID], [TransDate], [Total]) VALUES ('M0002', '2015-02-01 00:00:00', 100000), ('M0002', '2015-02-28 00:00:00', 222000), ('M0001', '2016-01-01 00:00:00', 150000), ('M0001', '2017-01-26 00:00:00', 160000), ('M0002', '2017-01-25 00:00:00', 160000), ('M0003', '2017-02-01 00:00:00', 9000) ; select MemberID,[2015], [2016], [2017] from ( select a.MemberID,a.name,a.DateJoin,year(b.TransDate)[year],b.Total from #Table1 A join #Table2 B on a.MemberID=b.MemberID ) src pivot ( count(total) for year in ([2015], [2016], [2017]) ) piv; 

Output

 MemberID 2015 2016 2017 M0001 0 1 1 M0002 2 0 1 M0003 0 0 1 

In the year 2000

 SELECT MEMBERID, COUNT(CASE WHEN YEAR=2015 THEN YEAR END ) AS [2015], COUNT(CASE WHEN YEAR=2016 THEN YEAR END ) AS [2016], COUNT(CASE WHEN YEAR=2017 THEN YEAR END ) AS [2017] FROM ( SELECT A.MEMBERID,A.NAME,A.DATEJOIN,YEAR(B.TRANSDATE)[YEAR],B.TOTAL FROM #TABLE1 A JOIN #TABLE2 B ON A.MEMBERID=B.MEMBERID)A GROUP BY MEMBERID 

Answer 2

try dynamic sql.

  --load in #temp table select MemberID , datepart (yyyy ,TransDate ) as TransDate ,COUNT(*)as cnt into #temp from [Transaction] group by MemberID , datepart (yyyy ,TransDate ) DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.TransDate) FROM #temp c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT MemberID, ' + @cols + ' from ( select MemberID , cnt , TransDate from #temp ) x pivot ( max(cnt) for TransDate in (' + @cols + ') ) p ' execute(@query) drop #temp -- cleanup of #temp table 

Answer 3

There seems to be no information from the member table. Therefore, select only the transaction table and specify conditionally.

 select memberid, count(case when year(transdate) = 2015 then 1 end) as [2015], count(case when year(transdate) = 2016 then 1 end) as [2016], count(case when year(transdate) = 2017 then 1 end) as [2017] from transaction group by memberid order by memberid; 

If you want to include members who do not have a transaction, you need a connection (external connection):

 select m.memberid, count(case when year(t.transdate) = 2015 then 1 end) as [2015], count(case when year(t.transdate) = 2016 then 1 end) as [2016], count(case when year(t.transdate) = 2017 then 1 end) as [2017] from member m left join transaction t on t.memberid = m.memberid group by m.memberid order by m.memberid;