claudio Posted May 25, 2006 Share Posted May 25, 2006 hi, perhaps i'm going crazy but...I've this problem.I don' know how to number the records of a resultset. I've this query:select A.id,A.descrizione as articolo,R.qt,R.um,R.importo,T.id_clienteC as id_cliente, C.ragsoc as cliente,C.indir,C.citta,C.cap,C.prov,'VENDITA' as tipoVendita,T.numero,T.anno,T.sezionalefrom articoli as A,ddt_testate as T,ddt_righe as R,clienti as C,sezionali as Swhere T.numero = R.numero and T.anno = R.anno and T.sezionale = R.sezionaleand R.id_articolo = A.id and T.id_clienteC = C.id and C.tipo = 'C'and A.id_casamadre = S.id_casamadre and T.sezionale = S.id and A.id_casamadre = 180 and R.causale = 'VENDITA'and T.annullato = '0'and T.data >= '2006-04-01' and T.data <= '2006-05-25'unionselect A.id,A.descrizione as articolo,R.qt,R.um,R.importo,T.id_clienteC as id_cliente, C.ragsoc as cliente,C.indir,C.citta,C.cap,C.prov,'OMAGGIO' as tipoVendita,T.numero,T.anno,T.sezionalefrom articoli as A,ddt_testate as T,ddt_righe as R,clienti as C,sezionali as Swhere T.numero = R.numero and T.anno = R.anno and T.sezionale = R.sezionaleand R.id_articolo = A.id and T.id_clienteC = C.id and C.tipo = 'C'and A.id_casamadre = S.id_casamadre and T.sezionale = S.id and A.id_casamadre = 180 and R.causale = 'OMAGGIO'and T.annullato = '0' and T.data >= '2006-04-01' and T.data <= '2006-05-25'and I've to introduce a counter who count the customers (clienti) but I don't know how to do this.can anyone helps me?thank you at all and excuse me for my bad english (I'm Italian) Claudio Link to comment Share on other sites More sharing options...
Jack McKalling Posted May 25, 2006 Share Posted May 25, 2006 (edited) Pff :)I know this is the syntax: SELECT customers FROM table WHERE something='something'But with your query?.. ehm..Isn't there any chance it could be shorter? :) Edited May 25, 2006 by Dan The Prof Link to comment Share on other sites More sharing options...
claudio Posted May 25, 2006 Author Share Posted May 25, 2006 ...but your query shows me the customers but doesnt number them...so thank you for the assistance Link to comment Share on other sites More sharing options...
claudio Posted May 25, 2006 Author Share Posted May 25, 2006 ...ehm I'm using Crystal Report 9.2 :ph34r: ...I forgot it Link to comment Share on other sites More sharing options...
Jack McKalling Posted May 25, 2006 Share Posted May 25, 2006 This query does result in the customers indeed, but the count can be done in the script that handles your queries :)Like in PHP: $result = mysql_query("SELECT customers FROM table WHERE something='something'");$count = mysql_num_rows($result);And thats all there is to it Link to comment Share on other sites More sharing options...
aspnetguy Posted May 25, 2006 Share Posted May 25, 2006 SELECT COUNT(A.id) AS Num, A.id...... Link to comment Share on other sites More sharing options...
pulpfiction Posted May 25, 2006 Share Posted May 25, 2006 If you are using COUNT(Fieldname) then you'll have to use GROUP BY clause.If you are writing this query as stored procedure then you can do one thing, store the result of the query in a temporary table and then count the number of rows like SELECT COUNT(*) FROM #temp. Link to comment Share on other sites More sharing options...
aspnetguy Posted May 25, 2006 Share Posted May 25, 2006 If you are using COUNT(Fieldname) then you'll have to use GROUP BY clause.If you are writing this query as stored procedure then you can do one thing, store the result of the query in a temporary table and then count the number of rows like SELECT COUNT(*) FROM #temp.<{POST_SNAPBACK}> Thanks, I forgot about the GROUP BY Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now