unions?

[Anon]

I want to count the number of times a certain fields have particular values. I tried using a union (which works in SQL Server) but it doesn't work in mySQL. All i get is a syntax error.

The correct way to do this or a better way would be appreciated.

select count() as count1 from projects where proj_type = '1'
union
select count() as count2 from projects where proj_type = '2'
union
select count() as count3 from projects where proj_type = '3'
union
select count() as count4 from projects where proj_type = '4'

[brandonschnell]

unions are listed in the mysql manual under "Things that have to be done sometime" & "Things that must be done in the real near future" so draw your own conclusion about the support UNIONs have under mysql.

i'd either rewrite the query or execute 4 different queries.

heres a dirty little sql hack:
select count(t1.proj_type) as count1, count(t2.proj_type) as count2, count(t3.proj_type) as count3, count(t4.proj_type) as count4 from projects t1, projects t2, projects t3, projects t4 where t1.proj_type = '1' and t2.proj_type = '2' and t3.proj_type = '3' and t4.proj_type = '4'

i don't think the syntax is correct, but the basic idea is to alias the table names and apply the count() & where on the aliases.

[brandonschnell]

nope, that idea doesn't work.

[Anon]

It gave back the same number for each type - 720, which is incorrect.

but it seems like the right things to do!

[[deleted]]

In MySQL,use IF() statements:

SELECT sum(if(proj_type='1',1,0)) as proj1,
sum(if(proj_type='2',1,0)) as proj2
FROM table

[smarlowe]

how about

select field, count(field) from table group by field

for starters?

[gimmieclubs]

You could always pull the individual selects into PHP arrays, and combine them, but then you'd have to de-dupe them too. Ok for small data sets, but anything big and it's far (far) from ideal. But until the UNION function is supported you don't have too many options.