CODEWITHSUNDEEP
sqlsql-server
Sawatvong Sriharuethai
Sawatvong Sriharuethai

Reputation: 11

Count value from database

I have below data. 

ID        UserID        Grade
1           1             A
2           1             A
3           1             a
4           1             a
5           1             b
6           1             C
7           1             c
8           1             b
9           2             b
10          2             C
11          1             b
12          2             A

I want below results.

RunningNumber UserID         Result         Count
1                1             a              4
2                1             b              1
3                1             c              2
4                1             b              2
5                2             b              1
6                2             c              1
7                2             a              1

The result will count column Grade (case insensitive) and must be group by UserID.

Upvotes: 0

Views: 83

Answers (4)

Sukanya Suku
Sukanya Suku

Reputation: 57

$query_not="SELECT count(status) AS sum FROM `user_leave_details`WHERE     (status='2' Or status='3') AND user_id_no='$user_id_no'";
$result=mysqli_query($bd,$query_not);
while($arr=mysqli_fetch_array($result))
{   
$sum=$arr['sum'];
}

connect.php
<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "";
$bd=mysqli_connect($mysql_hostname,$mysql_user,$mysql_password,$mysql_database);
?>

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

You can use DENSE_RANK for the RunningNumber, COUNT(*)OVER for the count per ID and ROW_NUMBER to take only the first row:

WITH CTE AS
(
    SELECT 
       RunningNumber = DENSE_RANK() OVER (ORDER BY UserID, Grade),
       UserID, Result = Grade,        
       [Count] = COUNT(*) OVER (PARTITION BY UserID, Grade),
       RowNum  = ROW_NUMBER()  OVER (PARTITION BY UserID, Grade ORDER BY ID)
    FROM dbo.TableName
)
SELECT RunningNumber, UserID, Result,  [Count]
FROM CTE
WHERE RowNum = 1

Demo

Upvotes: 1

Amit
Amit

Reputation: 15387

Use as below

 SELECT UserID, Grade, COUNT(ID) AS Count 
     FROM Table1
 GROUP BY UserID,Grade;

Updated

SELECT ROW_NUMBER() 
    OVER (ORDER BY Grade) AS RunningNumber , 
   UserID, Grade, COUNT(ID) AS Count 
     FROM Table1
 GROUP BY UserID,Grade;

Upvotes: 3

Raja Ram T
Raja Ram T

Reputation: 9071

Just use Group by & count 

SELECT  ID ,UserID,Grade 
COUNT(ID) FROM Tabel
GROUP BY Grade,UserID;

Upvotes: 1

Related Questions