I'm trying to build a SQL that will return the total amount of file for all the events.
DB:
id | name | filename1 | filename2
1 event1 1.jpg 2.jpg
2 event2 1.jpg Null
If you look at this scenario it should return "You have 3 files" but I have no idea how to make the SQL.
I'm using mySQL.
Thanks
You can unpivot or use UNION ALL on the data in the filename1 and filename2 fields and then get a total count. The UNION ALL takes the data from the multiple columns and converts it into multiple rows:
select count(files) Total
from
(
select name, filename1 as files
from yourtable
union all
select name, filename2
from yourtable
) src
Your table is not normalized and this is making your life difficult. When you have a one to many relationship like event:files, you need 2 tables, one for events and one for files belonging to events. Like this:
event_id int unsigned not null auto_increment
event_name varchar(45)
....
file_id int unsigned not null auto_increment
event_id int unsigned not null,
file_name varchar(45)
....
To get the total number of files, just do:
SELECT COUNT(*) FROM eventfiles;
If you want to get the number of files per event, do this:
SELECT e.event_name, COUNT(f.file_id)
FROM events e LEFT JOIN eventfiles f ON e.event_id=f.event_id
GROUP BY e.event_name;
You didn't specify what is in the column when there is no filename there, I assume that it is NULL then (as it should be).
I'm uncertain about the performance - but this should work in that case:
SELECT
SUM(CASE WHEN filename1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN filename2 IS NOT NULL THEN 1 ELSE 0 END) img_cnt
FROM event_table;
You could also get the count for individual events by event ID too:
SELECT event_id
SUM(CASE WHEN filename1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN filename2 IS NOT NULL THEN 1 ELSE 0 END) img_cnt
FROM event_table
GROUP BY event_id
Also, it is very true that this DB structure is not nice. It should be normalized, as dnagirl suggested!
