SQL Server Join Help (Basic)

Transact-SQL
1

I am on SQL Server 2012 and have a table that looks similar to below:
Table Name=Trans
Amount Itype hPlant PostDate
22.45 2 dplf 01/01/2016
45.55 2 dplf 01/01/2016
965.32 4 dplf 02/01/2016
14.25 3 dplf 01/01/2016
72.36 3 esch 01/01/2016
114147.77 2 dplf 01/01/2016
9641.25 4 dplf 01/01/2016
300.00 2 dplf 02/01/2016

I need to select the sum of amount with an ityp=2 and date=01/01/2016 and the sum of amount with itype=3 and date=01/01/2016 and plant=dplf plus some other data from various other tables.

How do I select the sums on 2 different criteria from one table. I have something similar to this:

select sum(tr.amount)
,sum(tr2.amount)
,convert(varchar,tr.date,1)+' ' +'TB Upload' Reference
,p.scode
from trans tr
inner join plant p on p.hmy=tr.hplant
inner join trans tr2 on tr2.hplant=tr.hplant
where tr.date='01/01/2016' and tr.itype=2
and tr2.itype=3 and tr2.date='01/01/2016'
group by tr.date,p.scode

my results are not the same as if I ran them individually. Please help!

2

solved with the following:
select sum(tr.amount)
,sum(case when tr.itype=2 then tr.amount else 0 end) as amt2
,sum(case when tr.itype=3 then tr.amount else 0 end) as amt3
,convert(varchar,tr.date,1)+' ' +'TB Upload' Reference
,p.scode
from trans tr
inner join plant p on p.hmy=tr.hplant
where tr.date='01/01/2016'
group by tr.date,p.scode

2 Likes
3

Yup, that's how I would have done it :slightly_smiling:

Beware of using dates in that format as SQL will parse them AMBIGUOUSLY depending on all sorts of things like Server locale, but also the Language of the currently connected user.

SET LANGUAGE US_ENGLISH
GO
SELECT CONVERT(date, '12/31/2016')
GO
SET LANGUAGE BRITISH
GO
SELECT CONVERT(date, '12/31/2016')   -- Error !!
GO

The only, unambiguous, format for string dates is 'yyyymmdd' - whatever the Server/User setting are SQl will always parse an 8-digit string using that format.