CODEWITHSUNDEEP
sql-servercount
StephenYo
StephenYo

Reputation: 69

Counting the number of times a session ID appears (MSSQL)

I am trying to put together a realtime view of users on our ticketing site.

Currently, I have 2 sessions on the site (Session col). My current query returns the following:

EVENTID     EVENT      Session    SessionOpened           SessionLength   NumberofTix
1619        BB King     11         2013-01-28 09:34:16.300   -2            2
1622        BB King     12         2013-01-28 09:36:55.073    0            1
1622        BB King     12         2013-01-28 09:36:55.077    0            3

However, it should look like this : 

EVENTID     EVENT      Session    SessionOpened           SessionLength   NumberofTix
1619        BB King     11         2013-01-28 09:34:16.300   -2            2
1622        BB King     12         2013-01-28 09:36:55.073    0            4

Why is Session 12 returning 2 rows- 1 with 1 ticket and 1 with 3 tickets?

The query:

SELECT distinct set_eSRO_Event as EventID, shName as EventName, setSRO_UID as SRO_SessionID, setDateTime as SessionStarted, 
DATEDIFF(mi, getDate(), setDateTime) as SessionTimer, Count(*) AS NumberofTickets FROM SessionTickets
INNER JOIN Shows on shCode = setShowCode
WHERE setStatus = 0
GROUP BY setsro_UID, shName, set_eSRO_Event, setDateTime

Upvotes: 1

Views: 590

Answers (2)

Lamak
Lamak

Reputation: 70638

Without really knowing wich column belongs to wich table, and the version of SQL Server, you can try the following query:

SELECT  EventID, 
        EventName, 
        SRO_SessionID, 
        MIN(SessionStarted) SessionStarted, 
        SessionTimer,
        SUM(NumberofTickets) NumberofTickets
FROM (  SELECT  set_eSRO_Event as EventID, 
                shName as EventName, 
                setSRO_UID as SRO_SessionID, 
                setDateTime as SessionStarted, 
                DATEDIFF(mi, getDate(), setDateTime) as SessionTimer, 
                Count(*) AS NumberofTickets 
        FROM SessionTickets 
        INNER JOIN Shows 
            ON shCode = setShowCode
        WHERE setStatus = 0
        GROUP BY set_eSRO_Event, shName, setSRO_UID, setDateTime,
                 DATEDIFF(mi, getDate(), setDateTime)) A
GROUP BY EventID, EventName, SRO_SessionID, SessionTimer

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

your result's columns and query's aliases are not coherent, so hard to say. I would imagine that setDateTime is different (= SessionStarted or SessionOpened). As you group by setDatetime, if they are different => two lines.

So add a MIN aggregate function to setDatetime (as in your "wanted" result), and remove setDatetime from GROUP BY

SELECT set_eSRO_Event as EventID, 
shName as EventName, 
setSRO_UID as SRO_SessionID, 
MIN(setDateTime) as SessionStarted, 
DATEDIFF(mi, getDate(), MIN(setDateTime)) as SessionTimer, 
COUNT(*) AS NumberofTickets 
FROM SessionTickets
INNER JOIN Shows on shCode = setShowCode
WHERE setStatus = 0
GROUP BY setsro_UID, shName, set_eSRO_Event

Upvotes: 1

Related Questions