CODEWITHSUNDEEP
sqlsql-server
William
William

Reputation: 61

Select sum with other table in SQL

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.

Upvotes: 5

Views: 127

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

It seems there is no information you need from table member. So select from table transaction alone and count 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 that don't have any transaction, then you do need a join (an outer join that is):

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;

Upvotes: 0

Chanukya
Chanukya

Reputation: 5873

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

Upvotes: 2

sandeep rawat
sandeep rawat

Reputation: 4957

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

Upvotes: 2

Related Questions