Sql Query to retrieve data using table

Question

I need query to get the count of participants in each level.

Note: participant who is in level 4 should not be in other levels ex: Level 3,2,1. I.e. I'm only interested in the highest level of each participant.

Table :

  +----+-------+----------+
  | ID | Level |  Date    |
  +----+-------+----------+
  | 38 | 1     |  06 -05  |
  | 38 | 2     |  08 -05  |
  | 38 | 3     |  12 -05  |
  | 38 | 4     |  13 -05  |
  | 39 | 1     |  13 -05  |
  | 39 | 2     |  13 -05  |
  | 40 | 1     |  12 -05  |
  +----+-------+----------+

Needed Output:

  +-------+-------+ 
  | Count | Level | 
  +-------+-------+ 
  | 1     | 1     |
  | 1     | 2     |
  | 0     | 3     |
  | 1     | 4     |
  +-------+-------+

participant ID 38 already existed in level 4 so that we consider only highest level — SOORYA
– SOORYA, Commented Jun 4, 2014 at 7:38

Dimitris Kalaitzis · Accepted Answer · 2014-06-04 07:41:32Z

2

Try this:

SELECT count(t2.Id) as count,t1.Level as level
FROM table1 t1
LEFT JOIN
(SELECT Id,MAX(Level) as Level
FROM table1
GROUP BY Id ) t2 on t1.Id=t2.Id and t1.Level=t2.Level
GROUP BY t1.Level

You can test it on SQL Fiddle

answered Jun 4, 2014 at 7:41

Dimitris Kalaitzis

1,4261 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SSC Over a year ago

What are the efficiency constraints in this query?

Dimitris Kalaitzis Over a year ago

If I understand your question correctly, the bottleneck, performance-wise is the join with the result set on the 2 fields.

Vignesh Kumar A · Accepted Answer · 2014-06-04 07:40:09Z

0

I think you're looking for

Select Count(*), Level FROM table_name GROUP BY Level

edited Jun 4, 2014 at 7:40

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

answered Jun 4, 2014 at 7:35

NcDreamy

8057 silver badges14 bronze badges

1 Comment

SOORYA Over a year ago

In this query we count participant id 38 in all levels but we need count only in level 4

Pipala · Accepted Answer · 2014-06-04 07:46:03Z

0

the expected result is to know how many people have a level as max level, grouping by level.

SELECT count(id) as count, level
FROM
(
  SELECT id, max(level) as level
  FROM myTable
  GROUP BY id
) t1
GROUP BY level;

answered Jun 4, 2014 at 7:46

Pipala

1972 silver badges10 bronze badges

1 Comment

SSC Over a year ago

This won't show the row where level is 3 and count is 0.

Collectives™ on Stack Overflow

Sql Query to retrieve data using table

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related