1

is't possible to set 2 counts in the same row. my result from query is like this: enter image description here

and i will that the end result seem like this : enter image description here and at the end build the precent count1 to count2

my attempt trough case was not successful : SELECT Date,Shift , CASE description WHEN 'Defects' THEN count ELSE 0 END AS Defect_Count , CASE description WHEN 'Total' THEN count ELSE 0 END AS Total_Count FROM ("Queries union)

Community
  • 1
  • 1
Qsec
  • 11
  • 4

2 Answers2

2

Here you go. Hope this helps. Thanks.

MYSQL:
select
    t.dates, t.shift,
    sum(case when t.description = 'Defects' then t.counts else 0 end) as `Defects`,
    sum(case when t.description = 'Total' then t.counts else 0 end) as `Total`
from (
    select *
    from tbl ) t
group by t.dates, t.shift
order by t.dates, t.shift

ORACLE:
SELECT dates, shift, defects , total
FROM
(
  SELECT *
  FROM tbl
)  
PIVOT
(
  sum(counts)
  FOR description IN ('Defects' as defects, 'Total' as total)
)  
ORDER BY dates

Result:
dates       shift Defects  Total
2018-01-20  AM      21     56
2018-01-20  PM      19     54
2018-01-23  AM      16     58
2018-01-23  PM      20     45
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
0

many Thanks is working for the first Step (counts in the same Row).

i will try now to build the percent (Defects to Total). Thanks.

to build the percent (defects to Total): select dates,shift,defects,total,round((100*defects/total),2) Percent2Total from(select t.dates, t.shift, sum(case when t.description = 'Defects' then t.counts else 0 end) as 'Defects', sum(case when t.description = 'total' then t.counts else 0 end) as 'Total' from ( select * from tbl ) t group by t.dates, t.shift )q order by dates,Shift. may be it's possible to build that only with Pivot or?

Qsec
  • 11
  • 4