SQL – combined SELECT queries and getting a % output

I am using SQLiteStudio and I am trying to run the following query. However, it isn’t returning a value. Can anyone help, please?

SELECT
(SELECT COUNT(t_record.LocationID)
  FROM t_record,
       t_location
 WHERE t_record.LocationID = t_location.LocationID AND 
       Y_AXIS >= 0 AND 
       Goal_for = 1)
       /
(SELECT COUNT(GoalID)
  FROM t_record
 WHERE t_record.Goal_for)
* 100

I have been asked to provide some of the data. Below I have included how I created my 3 tables and then some sample data for each section. Hopefully, this is enough but feel free to ask for more. These are the 3 tables I have created

CREATE TABLE t_location (
LocationID INT PRIMARY KEY,
X_Axis INT NOT NULL,
Y_AXIS INT NOT NULL
);

CREATE TABLE t_method (
MethodID INT PRIMARY KEY,
Body_Part VARCHAR(45) NOT NULL
);

CREATE TABLE t_record (
    GoalID     INT                PRIMARY KEY,
    LocationID [INT FORIEIGN KEY] REFERENCES t_location (LocationID),
    MethodID   [INT FORIEIGN KEY] REFERENCES t_method (MethodID),
    Time       INT                NOT NULL,
    Goal_for   BOOLEAN            NOT NULL

Data for the method table

INSERT INTO t_method 
VALUES (1,'Left Foot');

INSERT INTO t_method 
VALUES (2,'Right Foot');

INSERT INTO t_method 
VALUES (3,'Head');

Data for the location table – only included 3 locations.

INSERT INTO t_location 
VALUES (1, 0, -1);

INSERT INTO t_location 
VALUES (2, 0, 0);

INSERT INTO t_location 
VALUES (3, 0, 1);

Data for the record table, it is very pland but I’ve varied the location.

INSERT INTO t_record 
VALUES (1, 1, 1, 28, 1);

INSERT INTO t_record 
VALUES (2, 1, 1, 6, 1);

INSERT INTO t_record 
VALUES (3, 2, 1, 28, 1);

INSERT INTO t_record 
VALUES (4, 2, 1, 28, 1);

INSERT INTO t_record 
VALUES (5, 2, 1, 28, 1);

INSERT INTO t_record 
VALUES (6, 2, 1, 28, 1);

INSERT INTO t_record 
VALUES (7, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (8, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (9, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (10, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (11, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (12, 3, 1, 28, 1);

INSERT INTO t_record 
VALUES (1, 3, 1, 28, 1);

Thanks

Answer

Actually you had just a few syntax errors and correcting them plus adding something like * 1.0 to count() would do what you want (otherwise count() results are integer and in SQL server integer / integer would yield an integer which is 0 in your case).

This is your code, slightly modified to work:

SELECT
(SELECT COUNT(t_record.LocationID)
  FROM t_record,
       t_location
 WHERE t_record.LocationID = t_location.LocationID AND 
       Y_AXIS >= 0 AND 
       Goal_for = 1) * 1.0
       /
(SELECT COUNT(GoalID) 
  FROM t_record
 WHERE t_record.Goal_for=1)
* 100;

(I agree with preventing old style joins etc but was not the problem here). You could make it a little better:

SELECT
(SELECT COUNT(t_record.LocationID)
  FROM t_record,
       t_location
 WHERE t_record.LocationID = t_location.LocationID AND 
       Y_AXIS >= 0 AND 
       Goal_for = 1) * 100.0
       /
(SELECT COUNT(GoalID) 
  FROM t_record
 WHERE t_record.Goal_for=1) as Pct;

While the above code works, I think there is a better and cleaner way:

SELECT SUM(case when Y_AXIS >= 0 then 1 end) * 100.0 / COUNT(*)
   FROM t_record
   inner join t_location ON t_record.LocationID = t_location.LocationID 
   where Goal_for = 1;

You can also check what is really going on there with adding those fields:

SELECT 
   SUM(case when Y_AXIS >= 0 then 1 end) as YAXIS_GTE_ZERO,
   COUNT(*) as Total,
   SUM(case when Y_AXIS >= 0 then 1 end) * 100.0 / COUNT(*) as Pct
   FROM t_record
   inner join t_location ON t_record.LocationID = t_location.LocationID 
   where Goal_for = 1;

And here is DBFiddle demo using your example data

EDIT: And this is for YAXIS >= 0 and YAXIS < 0 as in your comment:

SELECT
      SUM(CASE WHEN Y_AXIS>=0 THEN 1 END)                   AS Opp_half_goals
    , SUM(CASE WHEN Y_AXIS<0 THEN 1 END)                    AS Own_half_goals
    , COUNT(*)                                              AS Total
    , SUM(CASE WHEN Y_AXIS>=0 THEN 1 END)* 100.0 / COUNT(*) AS Opp_half_Pct
    , SUM(CASE WHEN Y_AXIS<0 THEN 1 END) * 100.0 / COUNT(*) AS Own_half_Pct
FROM  t_record
      INNER JOIN t_location ON t_record.LocationID=t_location.LocationID
WHERE Goal_for=1;