Nobody laugh, but if you want a total for each vote, 1 to 5, and the total number of votes, all in one query(does a union still count as one query?), try this...
CREATE TABLE #temp ( MyID INT IDENTITY, Column1 INT ) INSERT INTO #temp ( Column1 ) VALUES ( 1 ) INSERT INTO #temp ( Column1 ) VALUES ( 2 ) INSERT INTO #temp ( Column1 ) VALUES ( 2 ) INSERT INTO #temp ( Column1 ) VALUES ( 3 ) INSERT INTO #temp ( Column1 ) VALUES ( 3 ) INSERT INTO #temp ( Column1 ) VALUES ( 3 ) INSERT INTO #temp ( Column1 ) VALUES ( 4 ) INSERT INTO #temp ( Column1 ) VALUES ( 5 ) SELECT Column1, COUNT(Column1) AS [Count] FROM #temp GROUP BY Column1 UNION SELECT Column1 = NULL, SUM(Column1) AS [Count] FROM #temp ORDER BY Column1 SELECT * FROM #temp --DROP TABLE #temp Ade, being half serious -----Original Message----- From: Damian Watson [mailto:[EMAIL PROTECTED] Sent: 11 June 2003 15:46 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] percentages of values in recordset That bit's easy enough. The column I'm selecting records from has 5 possible values (1-5). I'm trying to count the number of times the value appears in the column's recordset without writing 5 queries with the where clause. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 11, 2003 3:40 PM Subject: Re: [ cf-dev ] percentages of values in recordset > > (query.Whatever / query.RecordCount) * 100 > > ??? > > > Duncan Cumming > IT Manager > > http://www.alienationdesign.co.uk > mailto:[EMAIL PROTECTED] > Tel: 0141 575 9700 > Fax: 0141 575 9600 > > Creative solutions in a technical world > > ---------------------------------------------------------------------- > Get your domain names online from: > http://www.alienationdomains.co.uk > Reseller options available! > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > > > > "Damian Watson" > <[EMAIL PROTECTED] To: <[EMAIL PROTECTED]> > ign.co.uk> cc: > Subject: Re: [ cf-dev ] percentages of values in recordset > 11/06/2003 15:36 > Please respond to dev > > > > > > Thanks Rich, don't think I explained myself very well though. What I'm > trying to do is: > > SELECT myValue, COUNT(myValue) AS Whatever > FROM myTable > Where myValue=1 (and then on up to 5) > > Then output the count as a percentage of the whole recordcount. > > I realise I could loop it from 1 to 5 but myValue is already being looped > and I'm going to end up with a silly amount of queries generated > > Cheers > d > > ----- Original Message ----- > From: "Rich Wild" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, June 11, 2003 3:12 PM > Subject: RE: [ cf-dev ] percentages of values in recordset > > > > SELECT myValue, COUNT(myValue) > > FROM myTable > > GROUP BY myValue > > > > > -----Original Message----- > > > From: Damian Watson [mailto:[EMAIL PROTECTED] > > > Sent: 11 June 2003 15:21 > > > To: [EMAIL PROTECTED] > > > Subject: [ cf-dev ] percentages of values in recordset > > > > > > > > > Hi, > > > > > > Pretty dumbass question but I'm working on a poll where for > > > instance I have > > > a column with 5 possible values (1 to 5). > > > > > > Say there are 50 records. How do I get the number of records > > > for each value > > > without doing 5 queries? > > > > > > Cheers > > > d > > > > > > > > > > > > -- > > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
