CODEWITHSUNDEEP
sqlsql-server
gsiradze
gsiradze

Reputation: 4733

multiple group in select

It gives me 11 results

select min(e.UserId), e.Value from Table 
where UserId = 1 and EventTypeId = 3 and Value is not null 
group by e.Value

But if I'll query that:

SELECT e.UserId, e.Value, count(UserId) as UserIdCount
  FROM [G3CR_Test].[dbo].[EventLog] as e
  where EventTypeId = 3 and Value is not null and UserId = 1
  group by e.UserId, e.Value
  order by e.UserId

It gives a result 

UserId  Value   UserIdCount

1       X1             1
1       X2             1
1       X3             12
1       X4             1
1       X5             5
1       X6             1
1       X7             1
1       X8             1
1       X9             12
1       X10             1
1       X11             1

So instead of write directly 11 of UserIdCount it splits them. I know why it works that way (see link here)

But I want to group them at first by Value and than UserId. How is that possible?

I want result to be

UserId  Value   UserIdCount

1       X            11

Upvotes: 0

Views: 47

Answers (1)

LDMJoe
LDMJoe

Reputation: 1589

You're looking for a count of rows AFTER your initial query does the preliminary grouping, so we make your original query into a subquery, then do a count on that...

SELECT
    DerivedPreGrouped.UserId,
    COUNT(DerivedPreGrouped.UserIdCount) AS [Count]
FROM
    (
    SELECT 
        e.UserId, 
        e.Value, 
        count(UserId) as UserIdCount
    FROM 
        [G3CR_Test].[dbo].[EventLog] as e
    where 
        EventTypeId = 3 
        and 
        Value is not null 
        and UserId = 1
    group by 
        e.UserId 
        , e.Value
    /*
    order by 
        e.UserId
    --order by invalid in subquery
    */
    ) DerivedPreGrouped
GROUP BY
    DerivedPreGrouped.UserId
ORDER BY
    DerivedPreGrouped.UserId

Upvotes: 1

Related Questions