Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query Sum Group by

I have a table similar to structure below

table 
ID EmployeeID factor
1.  100       A
2.  100       B
2.  100       C

my requirement is for a given EmployeeID I need to replace the factor with numerical values and sum the factor where A = 1 , B=2 and C=3

using a select query I was able to replace the values and see the table

select employeeid,
       case factor
        WHEN 'F' THEN 4
        WHEN 'EC' THEN 2
        WHEN 'E' THEN 1
        ELSE 0
        END as factor
        From employee
        where employee_id = 100

but when I try to use sum and group by on this getting query syntax error

select employeeid,count(*) as count
       sum(case factor
        WHEN 'F' THEN 4
        WHEN 'EC' THEN 2
        WHEN 'E' THEN 1
        ELSE 0
        END as factor) as factor
        From employee
        where employee_id = 100
        group by employeeid,count
Tags:

2

Answers


  1. 0

    Use GROUP BY and HAVING to fetch data for particular employee after grouping data with that key.

    Also correct the SUM(CASE...END) statement

    So, SQL query should be:

    select employeeid,
    count(*) as count, 
    sum(
      CASE (factor)
        WHEN 'F' THEN 4
        WHEN 'EC' THEN 2
        WHEN 'E' THEN 1
        ELSE 0
    END
   ) as factor
From employee
group by employeeid
having employeeid = 100

    Sample Query: db<>fiddle

    Login or Signup to reply.
  2. 0 
    Create table Emp (ID int, EmployeeID int, factor nvarchar(2))

insert into emp(ID,EmployeeID,factor)
VALUES
(1,  100,       'A'),
(2,  100 ,      'B'),
(2,  100  ,     'C')
with CTE
AS
(
    select ID,
    EmployeeID,factor,
    case factor
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
        WHEN 'C' THEN 3
        ELSE 0
        END as factor2
    
    from emp
    where EmployeeID=100
)
select EmployeeID,factor,Sum(factor2) over(partition by EmployeeID) SumOfFactore
from CTE
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search