CODEWITHSUNDEEP
sql-servervb.netvisual-studiodatagridview
user7668852
user7668852

Reputation: 59

SQL query returns 0 for datagridview

I have a select statement that populates a datagridview:

da = New SqlDataAdapter("SELECT Month, (Sum(IncCnt)) as IncCnt, (Sum(AllFCR)) as AllFCR, (Sum(Prv)) as Prv, ((SUM(IncCnt)-SUM(AllFCR))/(SUM(IncCnt)-SUM(Prv))) as FCR, ((SUM(ASAT)+SUM(CSAT))/(SUM(SurveyTtl))) as CSAT FROM [tblPLOps_Data] group by Month", ScoreConn)

However, it displays a 0 for FCR and CSAT although you can do the math from the first three columns and see that FCR should be .89

I need to display as a percent anyway so I tried:

DataGridView1.Columns(4).DefaultCellStyle.Format = "p"

But that just returned 0%, I also changed to decimal and that just returned 0.0

Then I read online and tried:

DataGridView1.AutoGenerateColumns = True

But that didn't help either, anyway how to get it to return a value?

enter image description here

Upvotes: 2

Views: 41

Answers (1)

S3S
S3S

Reputation: 25112

((SUM(IncCnt)-SUM(AllFCR))/(SUM(IncCnt)-SUM(Prv)))

and 

((SUM(ASAT)+SUM(CSAT))/(SUM(SurveyTtl)))

are using INTEGER division since they are both integers. You need to cast (one of) them to decimal first, ideally the deominator. Here's an example...

select 1/2       RETURNS 0
select 1/2.0     RETURNS 0.5000000
select 1.0/2     RETURNS 0.5000000

Something like...

((SUM(IncCnt)-SUM(AllFCR))/(cast(SUM(IncCnt)-SUM(Prv) as decimal(16,2))))

and 

((SUM(ASAT)+SUM(CSAT))/(cast(SUM(SurveyTtl) as decimal(16,2))))

Upvotes: 4

Related Questions