Select sum with other table in SQL

Question

How do I select sum with other table if I have data like below:

Table Member

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 as a sum of how many times the member transaction in shop in years 2015-2017

The result I want it's:

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

How many members will appear in Result although don't have transaction too.

Answer 1

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 2

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