'Trying to count distinct elements in sql with case when statement
Hey all second semester comp sci student here. I decided to get in a little over my head with this database project I created. The database works but we didn't learn anything in class about "case when" statements with the count function but I'm trying to teach my self.
Here's the issue I'm trying to show how many of each book is sold but for some reason the count here isn't coming up correctly in my view that I created.
Here is the code to create the view:
--12. write 2 SQL views the set of view statements should include SELECT statements to view the views
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT( CASE bookNumber_FK WHEN (bookNumber = bookNumber_FK) THEN 1 ELSE 0 END) as Number_Sold
FROM Book
JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
SELECT *
FROM booksSold;
This code puts everything in one column like I want it to but it just isn't counting up everything properly it only goes up to 2 when I know some rows should have more than 2.
I'm also attaching a screenshot of MariaDB MySQL server screen so you can see what happens when I input the query.
As I have said was not taught this but just been trying to teach myself how to do it I've done a bunch of google searches and it seems like what I find is put individual results from the count(case when...) into different columns (not what I want) OR it leads me to showing how to count for single items. Maybe I'm not searching properly enough?
*EDIT request for sample data was added below:
INSERT INTO vvvv
INSERT INTO Book(bookNumber, bookTitle, bookAuthor, bookPrice, bookGenre, orderNumber_FK, publisherName_FK)
VALUES
("1", "STer WuRs", "Minestrone", 23.5, "Sci-Fi", "1", "Penguin Publishing"),
("2", "Waldo is Where?", "Branston", 15.25, "Children's", "2", "Capital Publishing"),
("3", "Parry Hotter", "Worling", 19.99, "Fantasy", "3", "Decent Publishing "),
("4", "Comic Sans the Comic", "Sansom", 12.99, "Graphic Novels and Comics", "4", "Pretty Darn Great Publishing"),
("5", "What is Email?", "Thompson", 99.99, "Textbooks/Informational", "5", "What is Email Publishing")
;
INSERT INTO BookSale(saleNum, employeeNumber_FK, customerNumber_FK, saleDate, saleAmount)
VALUES
("1", "4", "7", "2022-03-29", 38.75),
("2", "5", "6", "2022-04-03", 19.99),
("3", "1", "5", "2022-04-05", 56.48),
("4", "2", "4", "2022-04-06", 99.99),
("5", "3", "3", "2022-04-15", 99.99),
("6", "1", "2", "2022-04-19", 28.24),
("7", "2", "1", "2022-04-24", 132.97)
;
INSERT INTO isSoldIn(soldInNum, bookNumber_FK, saleNum_FK)
VALUES
("1", "1", "1"),
("2", "2", "1"),
("3", "3", "2"),
("4", "3", "3"),
("5", "4", "3"),
("6", "1", "3"),
("7", "5", "4"),
("8", "5", "5"),
("9", "2", "6"),
("10", "4", "6"),
("11", "3", "7"),
("12", "4", "7"),
("13", "5", "7")
;
CREATE TABLE vvvvvvvv
-- -- -- -- -- -- -- -- -- -- -- -- Book -- -- -- -- -- -- -- -- -- -- -- --
-- Creating the Book Table
-- Primary Key: bookNumber
-- Foreign Keys: orderNumber_FK, publisherName_FK
CREATE TABLE Book
(
bookNumber VARCHAR(5) NOT NULL,
bookTitle VARCHAR(50),
bookAuthor VARCHAR(50),
bookPrice DOUBLE,
bookGenre VARCHAR(50),
orderNumber_FK VARCHAR(5),
publisherName_FK VARCHAR(30),
CONSTRAINT Book_PK PRIMARY KEY (bookNumber),
CONSTRAINT Book_FK1 FOREIGN KEY (orderNumber_FK)
REFERENCES PublisherOrders (orderNumber)
ON DELETE CASCADE,
CONSTRAINT Book_FK2 FOREIGN KEY (publisherName_FK)
REFERENCES PublisherCompany (publisherName)
ON DELETE CASCADE
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- BookSale-- -- -- -- -- -- -- -- -- -- --
-- Creating the BookSale Table
-- Primary Key: saleNum
-- Foreign Keys: employeeNumber_FK, customerNumber_FK
CREATE TABLE BookSale
(
saleNum VARCHAR(5) NOT NULL,
employeeNumber_FK VARCHAR(5),
customerNumber_FK VARCHAR(5),
saleDate DATE,
saleAmount DOUBLE,
CONSTRAINT BookSale_PK PRIMARY KEY (saleNum),
CONSTRAINT BookSale_FK1 FOREIGN KEY (employeeNumber_FK)
REFERENCES Employee (employeeNumber)
ON DELETE CASCADE,
CONSTRAINT BookSale_FK2 FOREIGN KEY (customerNumber_FK)
REFERENCES Customer (customerNumber)
ON DELETE CASCADE
);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- IsSoldIn -- -- -- -- -- -- -- -- -- -- --
-- Creating the IsSoldIn Table
-- Primary Keys: soldInNum, bookNumber_FK, saleNum_FK
-- Foreign Keys: bookNumber_FK, saleNum_FK
CREATE TABLE IsSoldIn
(
soldInNum VARCHAR(5) NOT NULL,
bookNumber_FK VARCHAR(5),
saleNum_FK VARCHAR(5),
CONSTRAINT IsSoldIn_PK PRIMARY KEY (soldInNum),
CONSTRAINT IsSoldIn_FK1 FOREIGN KEY (bookNumber_FK)
REFERENCES Book (bookNumber)
ON DELETE CASCADE,
CONSTRAINT IsSoldIn_FK2 FOREIGN KEY (saleNum_FK)
REFERENCES BookSale (saleNum)
ON DELETE CASCADE
);
Solution 1:[1]
There's no need for the CASE expression. Just count the number of rows in the joined table.
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT(*) as Number_Sold
FROM Book
JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
If you want zero counts for books that haven't been sold, you need to use LEFT JOIN.
CREATE VIEW booksSold
AS
SELECT
bookNumber,
bookTitle,
bookPrice,
COUNT(isSoldIn.bookNumber_FK) as Number_Sold
FROM Book
LEFT JOIN isSoldIn ON Book.bookNumber = isSoldIn.bookNumber_FK
GROUP BY bookNumber;
COUNT(*) has to change to COUNT(isSoldIn.bookNumber_FK) so that the row with null values from the isSoldIn table because there's no match aren't counted.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |