Who to return two counts in the recordset?

[CroCrew]

Hello Everyone,

Let me start by thanking you all for your help.

Funny but, I have never had the need to return the sum of records in a table for each entity also with that a second column to have a total from a bit field.

Let me explain:

The table has three fields and each record is information for a ticket.
KeyID: an auto incrementing field
MemberID: Integer value
Sold: Bit value (1 is the ticket has been sold)

So for this example let’s say the data looks like this:
1 – 1 – 0
1 – 3 – 1
1 – 2 – 1
1 – 3 – 1
1 – 3 – 0
1 – 2 – 1
1 – 4 – 1
1 – 3 – 1

What I am trying to return from a single query is:
MemberID – TotalTickets – TotalSold
1 – 1 – 0
2 – 2 – 2
3 – 4 – 3
4 – 1 – 1

This is what I have so far. But I can’t figure out how to get the third value to return in the recordset.

SELECT MemberID, COUNT(KeyID) As TotalTickets, '???' As TotalSold
FROM Tickets
GROUP BY MemberID
ORDER BY MemberID


Thanks again for any help that you can give.

CroCrew~

[gershwyn]

Maybe I'm misunderstanding your question, but if the sold field can only be 0 or 1, can't you just sum it to get the total?

SELECT MemberID, COUNT(KeyID) As TotalTickets, SUM(Sold) As TotalSold
FROM Tickets GROUP BY MemberID ORDER BY MemberID

[CroCrew]

Sold is a bit data type. A sum operation cannot take a bit data type as an argument.

[CroCrew]

Thanks Gershwyn,

You got me thinking.. And this works:

SUM(CAST(Sold as int)) As TotalSold

Thanks again,
CroCrew~