ListValueCount() type function..

[webmigit]

Is there a way in SQL to say...

Select ListValueCount(ColumnName,"value&quot FROM Table?

Is there a function that will do this.. any way to fake it.. I can run code side.. but that requires a lot more coding than it justifies..

I'm running a polling system and just for kicks, I want to show a graph of who's voted for what..

Tony Did I help?
Vote!

 

[sunila7]

Hi,

What eaxactly r u looking for... What exactly would a ListValueCount() funtion do?.... I didnt understand question... looks as if u r looking for a query which will count the no of records for a column which has a specific value.... if that is the case then try...

SELECT Count(ColumnName) From table where ColumnName = 'Value'

Sunil

 

[webmigit]

Oops duh.. ListValueCount(List_Normally_but_the_sql_version_would_be_query_column_here,"value_to_find_in_query_column_as_a_whole_ie_a_would_get_a_but_not_and_or_bad_or_aa_etc&quot

Its a cold fusion function.. is there an sql function like it?

Sorry.. forgot to explain that..

Does this help?

Tony Did I help?
Vote!

 

[r937]

that indeed is a neat cf function

sunil had it right with count(col)

you can also use

select count(*)
from yourtable
where col = 'value'

which will be slightly faster in many cases

count(*) counts rows, and the database can often get that from its system tables or an index, whereas count(col) counts non-null values

in this case they should be equivalent, since the WHERE clause will exclude non-null values automatically

rudy

http://rudy.ca/

 

[webmigit]

But its querying a poll table with one pollid but multiple pollchoiceids.. so count(pollid) gives me the total number from all choices.. I want some to group them so that each row returns the choiceid and the # of users that selected that option.. IE...

Choice 1 10 votes
Choice 2 18 votes
Choice 3 2 votes
Choice 4 30 votes

Whereas all queries I've tried (if they work at all) return 60 if this sample data was real..

Any ideas anyone?

Tony Did I help?
Vote!

 

[wengyan]

select col,count(*)
from yourtable
group by col WENG YAN

 

[webmigit]

cfgraph doesn't support grouping.. is that the only way? Did I help?
Vote!

 

[r937]

i've never used cfgraph but the example on macromedia's site specifically does involve grouping --

http://livedocs.macromedia.com/cf50docs/CFML_Reference/Tags37.jsp#2031354

the query is actually a query of queries, but you won't need to do that --

<cfquery dbtype = &quot;query&quot; name = &quot;DataTable&quot;>
SELECT
Department_Name,
AVG(Salary) AS AvgSal,
SUM(Salary) AS MinSal
FROM GetSalaries
GROUP BY Department_Name
</cfquery>

see the groups?

now the graph --
<cfgraph type = &quot;bar&quot;
query = &quot;DataTable&quot;
valueColumn = &quot;AvgSal&quot;
itemColumn = &quot;Department_Name&quot;
title = &quot;Average Salary by Deparment&quot;
scaleTo = 120000
fileFormat = &quot;gif&quot;
depth = 5
colorList = &quot;red,green,blue,yellow,orange&quot;>
<cfgraphdata label = &quot;Facilities&quot; value = 35000>
</cfgraph>


helps??? VOTE FOR ME!!!!


rudy

 

[wengyan]

I am very poor of cold fusion
I changed my solution,is it help for you?

create view v_tmp
AS select col,count(*)
from yourtable
group by col

select * from v_tmp WENG YAN