CODEWITHSUNDEEP
mysqlsql
Yannick
Yannick

Reputation: 3663

How to create a SQL statement to count value from rows

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

Upvotes: 0

Views: 77

Answers (3)

dnagirl
dnagirl

Reputation: 20456

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:

events

event_id int unsigned not null auto_increment
event_name varchar(45)
....

eventfiles

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;

Upvotes: 1

ppeterka
ppeterka

Reputation: 20726

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!

Upvotes: 0

Taryn
Taryn

Reputation: 247710

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

See SQL Fiddle with Demo

Upvotes: 5

Related Questions