summing up according to 2 columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

summing up according to 2 columns

Author

Topic

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 16:48:10

Hi, I've got an sql query issue (MS-SQL)
I have a table with 3 columns
the table looks like this:
TicketID TicketHolder TicketCounter
1 Joe 4
1 Tom 2
3 Joe 1

as you can see, each ticket ID might be listed under more than one ticket holder
whoever has the largest TicketCounter is considered to be that TicketID's owner
I want to run a query which will bring how many tickets are held by each person (if they hold any of course)

I ran this query but it's bringing me also non ticket owners - any ideas ?

select TicketHolder, COUNT(TicketID) as TicketsOwned from Tickets group by TicketHolder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-27 : 16:54:16

Not quite sure as you didn't provide expected output, but perhaps add this to the bottom of your query:
HAVING COUNT(*) > 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 16:56:42

my problem is that the query doesn't take into account ticket ownership ... it lists Joe correctly with 2 tickets owned by him , but also provides a false account of 1 ticket held by Tom

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-27 : 17:01:07

I don't see how that can be with the query you've used. Perhaps you aren't showing us everything or explaining it properly. The simple query you've got and the sample data you provided would mean that the Tom data returned is correct.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 17:04:10

Under Ticket ID 1 you have two ticket holders:
joe who's counter is 4
and tom who's counter is 2
since joe's counter is higher he is considered to be the ticket owner
(the ticket with the ID of 1)

what I want to do is to summerize how many tickets are OWNED per person, and I'm sure my query is wrong :)
any ideas on what should be the correct query ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-27 : 17:18:22

Please provide a full set of sample data plus expected output using that same sample data. This is how you make your problem clear to us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 17:21:11

TicketID TicketHolder TicketCounter
1 Joe 4
1 Tom 2
1 David 3
2 David 7
3 Joe 3
3 Tom 1

the output should be
Joe 2
David 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-27 : 17:36:30

Here is one way:

DECLARE @Ticket TABLE (TicketID INT, TicketHolder VARCHAR(50), TicketCounter INT)

INSERT @Ticket VALUES
(1, 'Joe', 4),
(1, 'Tom', 2),
(1, 'David', 3),
(2, 'David', 7),
(3, 'Joe', 3),
(3, 'Tom', 1)


SELECT 
    TicketHolder,
    COUNT(*) AS TicketCount
FROM    
    (    
        SELECT 
            TicketHolder, 
            ROW_NUMBER() OVER(PARTITION BY TicketID ORDER BY TicketCounter DESC) AS RowNum
        FROM
            @Ticket   
    ) AS Ticket
WHERE    
    RowNum = 1  
GROUP BY
    TicketHolder

liran-e
Starting Member

8 Posts

Posted - 2010-12-27 : 17:47:10

excellent :)
this is exactly what I needed
thanks a lot ! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources