How can I create a view for my desired output-CodePudding

What should I include in my view so that it outputs number of transactions in September for employee id 4:

SELECT * FROM DeniseTransactions;

gives the output:

  CREATE TABLE Transactions(
    e_id INT,
    c_id INT,
    l_id INT,
    date DATE,
    t_id INT,
    PRIMARY KEY(t_id),  
    FOREIGN KEY(c_id) references Customers(c_id),
    FOREIGN KEY(e_id) references Employees(e_id),
    FOREIGN KEY(l_id) references Locations(l_id)
    );

    INSERT INTO Transactions VALUES(1,3,1,'2021-08-09',1);
    INSERT INTO Transactions VALUES(4,2,2,'2021-08-14',2);
    INSERT INTO Transactions VALUES(4,4,1,'2021-09-07',3);
    INSERT INTO Transactions VALUES(3,4,1,'2021-09-07',4);
    INSERT INTO Transactions VALUES(4,1,3,'2021-09-07',5);
    INSERT INTO Transactions VALUES(1,4,1,'2021-09-23',6);

I know its incorrect but this is what i have so far. Just struggling to get further

CREATE VIEW DeniseTransactions AS
    SELECT COUNT(transactions)
    FROM Transactions
    WHERE e_id = '4';

CodePudding user response：

You can create the view as:

create view DeniseTransactions as
select count(*) as number_of_transactions
from Transactions
where t_id = 4
  and date >= '2021-09-01' and date < '2021-10-01';

Result:

number_of_transactions
----------------------
1

See running example at DB Fiddle.

CodePudding user response：

you need to do count(*):

CREATE VIEW DeniseTransactions AS
    SELECT COUNT(*) number_of_transactions
    FROM Transactions
    WHERE e_id = '4'
    AND date >= '2021-09-01' AND date < '2021-10-01';