multiple group in select

Question

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

Edit your question and provide sample data and desired results. It is rather hard to decipher what you may be asking. — Gordon Linoff, Jan 15 '16 at 13:18
What is `X`? It is shown as a distinct value in the first result set, so what do you want to get in the second case? — Alex, Jan 15 '16 at 13:22
@AlexanderMP It's ID number. It's no matter about question. There are many same X in db so I group them to get distincts. — gsiradze, Jan 15 '16 at 13:23
Your second query seems sound. Are all the values of `X` in your `Value` column not actually the same? Like, would it be more accurate to describe them as `X1`, `X2`,...`X11`? Your `GROUP BY` only has those two fields, so it's the only thing I can think of that would cause the results you're reporting. — LDMJoe, Jan 15 '16 at 13:24
`1 X 11` that is the result you want to get. What is `X` in this case? — Alex, Jan 15 '16 at 13:24
@AlexanderMP X doesn't matters in that case. It must be grouped at first to not get same values and then I want count of them. — gsiradze, Jan 15 '16 at 13:26
If I'll remove group of e.Value then it returns 37 of count (so they're not distinct values) — gsiradze, Jan 15 '16 at 13:28

LDMJoe · Accepted Answer · 2016-01-15T13:35:12.667

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

If I'll remove group of e.Value then it returns 37 of count (so they're not distinct values) — gsiradze, Jan 15 '16 at 13:28

multiple group in select

1 Answers1