I'm hoping I can explain this well enough so that I can get my point across.
What I need to do is get the counts of file names with certain words in them... This is my query right now(which I know is wrong, just not sure how to approach it)
select projectname as Project, count(distinct(filename)) as MainCount,
count(distinct(filename)) as AltCount, count(distinct(filename)) as VideoCount,
count(distinct(filename)) as PsCount
from image_archive
where projectname like "<Name of project>"
and filename like "%ALT%"
And this gives me the following output:
Obviously, all the counts are going to be the same because of the where clause and the fact that the select statement is the same count for the same column 4 different times. (That was my attempt at trying to show what I'm trying to accomplish) What I'm TRYING to accomplish is to get the counts of each of the filenames where the MainCount is a filename with no "_" and ends with a number, the alts will end in "_ALT%", Videos will end with "_VID", and so on.
I thought I'd be able to to do something like
Select projectname as Project, count(distinct(filename) like "%ALT"%) as AltCount
but that doesn't seem to be the case. any help would be greatly appreciated!
