Help with Query -- Count ??

[tonye53]

I have a problem I need some help with. I have a table that keeps trades for the day:
TDate TTime Type Amount. The type is L for Long, S for short. Multiple trades occur each day. I want to summarize each day to a single record:

Date TradeCnt Longs Shorts Amount

My problem is how do I count the number of Long (L) trades and the number of Short (S) trades. Heres where I'm at:

SELECT TDate, count(TDate), ??Longs Shorts?? Sum(Amount) FROM tblTrade GROUP BY TDate

How do I do the Longs and shorts, thanks in advance !!

Tony

 

[Ryko]

Use an UNION query, something like

SELECT TDate, Sum(L) As Longs, Sum(S) As Shorts, Sum(A) As Amount FROM
(
SELECT TDate, Count(*) As L, 0 As S, Sum(Amount) As A FROM tblTrade WHERE Type = "L" GROUP BY TDate
UNION ALL
SELECT TDate, 0 As L, Count(*) As S, Sum(Amount) As A FROM tblTrade WHERE Type = "S" GROUP BY TDate
) As X
GROUP BY TDate

HTH,
Ryko

 

[tonye53]

Thanks Ryko, but in my table I have only one column for TYPE which contains either 'S' or 'L'. In my output I want two separate totals - one 'Longs' and one 'Shorts'.

 

[Jon K]

SELECT [TDate], Count([TDate]) as TradeCnt, -Sum([Type]="L") AS Longs,
-Sum([Type]="S") AS Shorts, Sum([Amount]) AS SumOfAmount
FROM [tblTrade]
GROUP BY [TDate]

.

 

[tonye53]

Jon, Thanks so much!! Solved my problem.