Is it Possible to combine these queries?

[alsjka]

Hi i have these queries that each count an item. Is it possible to combine these into one query that counts the items? Instead of having 6 different queries?


<cfquery name=&quot;get_data_info_1&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Shipped to wrong location'
</cfquery>
<cfquery name=&quot;get_data_info_2&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Received damaged'
</cfquery>
<cfquery name=&quot;get_data_info_3&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Received item late'
</cfquery>
<cfquery name=&quot;get_data_info_4&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Received wrong parts'
</cfquery>
<cfquery name=&quot;get_data_info_5&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Parts manufactured wrong'
</cfquery>
<cfquery name=&quot;get_data_info_6&quot; datasource=&quot;mp2&quot;>
SELECT count(error) as error_count
from vendor_issue
where error = 'Other'
</cfquery>

 

[Wullie]

Hi mate,

Not sure I understand what count you want, whether single or all combined:

What about:

SELECT count(error) as error_count
from vendor_issue
where error = 'Shipped to wrong location'
OR error = 'Received damaged'
OR etc etc etc

Hope this helps

Wullie

http://www.tutorial-world.com

http://www.freshlookdesign.co.uk

http://www.necomputers.org.uk

The pessimist complains about the wind. The optimist expects it to change.
The leader adjusts the sails. - John Maxwell

 

[erwino]

I You should change the setup of that table to create a table with error descriptions:
vender_issue should store the id of the error description table ie error_id = 1, error_desc = 'Shipped to wrong location', error_id = 2, error_desc = 'Received item late' etc
then you can create a query that
SELECT count(error) as error_id
from vendor_issue
where error_id IN (1,2,3,4,5,6,..n)
Later when you change the description of the error your query still works.

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[alsjka]

i'll try to explain better. I want to tell someone how many different errors types have been entered. So instead of having the 6 different queries i am just curious if it was/is possible to have one query give the results of different error types.

select count(iif(error eq 'shipped to wrong location') as error_id_1,count(iif(error eq 'received item late') as error_id_2 and so on.

hope this exaplains it better..

 

[r937]

yup, just one query:

[tt]<cfquery name=&quot;allinone&quot; datasource=&quot;mp2&quot;>
select error
, count(*) as error_count
from vendor_issue
group
by error
</cfquery>[/tt]

each error will be on its own row in the result set, so you can print them all with --

[tt]<table>
<cfoutput query=&quot;allinone&quot;>
<tr><td>#allinone.error#:</td>
<td>#allinone.error_count#</td></tr>
</cfoutput>
</table>[/tt]

rudy

http://rudy.ca/

 

[alsjka]

duh where was my head i forgot about just grouping everything.

thanks