-2

I am using postgresql to display QA% in my project. Now issue is that, QA% for one of the user is showing wrong data.

this is my calculation I am using to show QA%

(to_char(( (coalesce(s_qa_num,0) + coalesce(s_qa_num_adtl,0))/nullif(coalesce(s_qa_den,0) + coalesce(s_qa_den_adtl,0),0))*100,'999D99')||'%')::character varying as qa_pct

Output from the view I have created

https://pastiebin.com/5db2d1589ef6b

the view I am using for this

https://pastiebin.com/5db2d1cb48822

the query I run in pgadmin

select * from mystats_in_eligbility.vw_m_quality where empl_id = '329509' 
AND dte BETWEEN '2019-03-23' AND '2019-09-06'
order by dte desc;

Current result for QA% is 97.53% expected result is 96.34%.

5
  • Let me suggest to use some on-line fiddle like this Create the table and add inserts with your sample data.
    – McNets
    Commented Oct 25, 2019 at 10:29
  • 1
    Your pastbin is difficult to interpret. As @McNets suggests - we need sample tables (CREATE TABLE blah...) and data (INSERT INTO blah VALUES...). And please give us the full SQL statement - context is important! I have a few articles on how to ask questions here on my profile - you might want to take a look? p.s. welcome to the forum! :-)
    – Vérace
    Commented Oct 25, 2019 at 10:37
  • @McNets I have updated Commented Oct 25, 2019 at 11:06
  • @Vérace please check updated question Commented Oct 25, 2019 at 11:06
  • 1
    There's no data (INSERT INTO) for us to look at to check the validity of the calculation. Furthermore, you have )||'%')::character but a search of both pastebins for the % character gives nothing so I'm wondering where it is? Your query is very large and it would appear to be very tricky to zone in on your QA% problem - is there any way you could produce a Minimum, Complete Verifiable Example?
    – Vérace
    Commented Oct 25, 2019 at 11:32

1 Answer 1

1

I built a fiddle with your initial data ... http://sqlfiddle.com/#!17/ab139/1

@Verace the '%' is added to the result to make it appear a percentage number

still I don't get your result ... I assume you create the arithmetic middle of your QA - because with your query I still get one row per day..

SELECT ( 
    to_char( 
        ( ( (
          SUM( s_qa_num) / SUM (s_qa_den) 
         )*100)
      ),'999D99')||'%')::character varying as qa_pct FROM t;

this far easier Select produces your desired result ...

still your view in pastebin seems to lack some lines - as your select includes data from late march to september, your pastebin starts in early april

2
  • And why put the % into the string in the SQL? That's a client issue. It really messes up the SQL!
    – Vérace
    Commented Oct 25, 2019 at 19:17
  • I see it from the same point
    – eagle275
    Commented Oct 28, 2019 at 7:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.