Return 0 instead of nothing in query

[jucooper1]

I have a database I started for the football team I coach. Looking at trends and stats. I have a query that I am trying to run that lists run and pass plays grouped by down and distance group. I've used a bunch of unions to get the information in the order/format I want. Now, the problem is when a certain group is zero, nothing gets returned for that down and distance group. I have tried using NZ(count(gn),0), NZ(count(gn))+0, iif(count(gn) IS NULL, 0, count(gn)). None of it seems to work. Below is a section of my code. I know that for this team (Offense) that when dn is 3 and dg is L, there should be 2 passes and 0 runs, but it returns no values at all for that group. Is there any work around to fix this, or a better way to give me the same data without the subquery and union? Any help is appreciated, thanks!


SELECT Dn, Dg, count(gn) AS Run, (SELECT count(gn)
FROM DataT


WHERE offense = "Marian" AND PlayType = "Pass"
GROUP BY dn, dg
HAVING dn = 3 AND dg = "L") AS Pass, Format((run/(run + pass)),"Percent") AS [Run%]
FROM DataT
WHERE offense = "Marian" AND PlayType = "Run"
GROUP BY dn, dg
HAVING dn = 3 AND dg = "L"
UNION ALL
SELECT Dn, Dg, count(gn) AS Run, (SELECT count(gn)
FROM DataT
WHERE offense = "Marian" AND PlayType = "Pass"
GROUP BY dn, dg
HAVING dn = 3 AND dg = "M") AS Pass, Format((run/(run + pass)),"Percent") AS [Run%]
FROM DataT
WHERE offense = "Marian" AND PlayType = "Run"
GROUP BY dn, dg
HAVING dn = 3 AND dg = "M"

[ranman256]

I convert null to zero with a custom function to catch fields = '' but not null:

usage: NZZ([FIELD])

Public Function Nzz(ByVal pvAmt)
On Error Resume Next
Select Case True
  Case IsNull(pvAmt)
    Nzz = Val(Nz(pvAmt, 0))
  Case ""
     Nzz = 0
  Case Else
     Nzz = pvAmt
End Select
End Function

[CJ_London]

Would help if an example of what you are getting and what you require was provided.

I suspect the issue is you want all records returned even if there is no result. Ie. You have Missing records. If this is the case you would need to left join another table that lists the teams you do want

[jucooper1]

There are 4 downs (dn) (1, 2, 3, 4) and 4 distance groups (dg) (XL, L, M, S) and I would like a row for each, but for example when dn is 3 and dg is L, there should be 2 passes and 0 runs, but because of the null in runs, there is no row for that group. I would like a row for each, with zeroes for nulls.

[jucooper1]

I'm not real familiar with custom functions

[kd2017]

1. What is gn?

2. I'm not sure why you need unions here. And I suspect your tables aren't properly normalized. Can you post a screenshot of your relationship window? Or post your db?

3. Looking at your sql is the playtype='run' necessary in the outer select statements?

SELECT Dn, 
       Dg, 
       count(gn)                                   AS Run, 
       (SELECT count(gn) 
        FROM   DataT 
        WHERE  offense = "Marian" 
               AND PlayType = "Pass" 
        GROUP  BY dn, 
                  dg 
        HAVING dn = 3 
               AND dg = "L")                       AS Pass, 
       Format(( run / ( run + pass ) ), "Percent") AS [Run%] 
FROM   DataT 
WHERE  offense = "Marian" 
       AND PlayType = "Run" 
GROUP  BY dn, 
          dg 
HAVING dn = 3 
       AND dg = "L" 
UNION ALL 
SELECT Dn, 
       Dg, 
       count(gn)                                   AS Run, 
       (SELECT count(gn) 
        FROM   DataT 
        WHERE  offense = "Marian" 
               AND PlayType = "Pass" 
        GROUP  BY dn, 
                  dg 
        HAVING dn = 3 
               AND dg = "M")                       AS Pass, 
       Format(( run / ( run + pass ) ), "Percent") AS [Run%] 
FROM   DataT 
WHERE  offense = "Marian" 
       AND PlayType = "Run" 
GROUP  BY dn, 
          dg 
HAVING dn = 3 
       AND dg = "M"

https://www.dpriver.com/pp/sqlformat.htm

[jucooper1]

1. Gn is gain, yards gained. I use that for all of the aggregate functions (count is attempts, sum is yards and avg is average).

2. This table has no relationships. I set it up with relationships, but I import to it from Excel and the fields that have relationships come in blank, so I had to remove them. (So if you have any suggestions on that I am all ears.)

I had to use unions for each combination because when I tried it without a union I get error messages every way I tried.

3. I think so, otherwise it would count all runs and passes.