Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #10466 > unrolled thread

sql how to have the total rows number in this case (this is correct question)

Back to article view | Back to comp.lang.php

Contents

  sql how to have the total rows number in this case (this is correct question) nawfer <novalidsforspam@alt.al> - 2013-08-09 16:32 +0200
    Re: sql how to have the total rows number in this case (this is correct question) nawfer <novalidsforspam@alt.al> - 2013-08-09 16:39 +0200
      Re: sql how to have the total rows number in this case (this is correct question) Jerry Stuckle <jstucklex@attglobal.net> - 2013-08-09 13:31 -0400

#10466 — sql how to have the total rows number in this case (this is correct question)

in this select 
$query="SELECT * 
FROM table1
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC;


how to have
a) the numeration for every row;
b) the total number of all the rows 
c) the total number of column2 = 100 rows
d) the total number of column3='Y'   rows   


with the code I have that the rows are ordered and the rows with 100 and y
are sended at the end (first 100)

1
2
3
4
5
6 			
7 			
8 
1    100 		
1    100 
5    100 
7    100
2         y	
4         y
5         y


I know that exist the COUNT function, but so I must to executte the sql
more time;
I need execute sql only one time and can to have the rows number;
I thinked one solution can to be to fill a 4 column with the separated
numeration



I would like to have this 

a             1          
b             2
b             3
c             4
c             5
d             6
e             7
e             8
f             9 			
g             10 			
h             11 
a    100      1 		
a    100      2
e    100      3
g    100      4
b         y   1	
d         y   2
e         y   3



or not know if whith the same sql can at the end created two column or
table so
total   col 1           11
total   col 2(the100)    4
total   col 3(the y)     3

[toc] | [next] | [standalone]

#10467

Il Fri, 9 Aug 2013 16:32:35 +0200, nawfer ha scritto:

> in this select 
> $query="SELECT * 
> FROM table1
> ORDER BY
> (CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
> 2 ELSE 0 END) ASC, column1 ASC, column2 ASC;
> 
> 
> how to have
> a) the numeration for every row;
> b) the total number of all the rows 
> c) the total number of column2 = 100 rows
> d) the total number of column3='Y'   rows   


pheraps tips is 'simple' add for every case some i++ x++ z++?
i++  for row normal
x++  for case WHEN column2 = 100
z++  for case WHEN column3 = y  ?

[toc] | [prev] | [next] | [standalone]

#10468

On 8/9/2013 10:39 AM, nawfer wrote:
> Il Fri, 9 Aug 2013 16:32:35 +0200, nawfer ha scritto:
>
>> in this select
>> $query="SELECT *
>> FROM table1
>> ORDER BY
>> (CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
>> 2 ELSE 0 END) ASC, column1 ASC, column2 ASC;
>>
>>
>> how to have
>> a) the numeration for every row;
>> b) the total number of all the rows
>> c) the total number of column2 = 100 rows
>> d) the total number of column3='Y'   rows
>
>
> pheraps tips is 'simple' add for every case some i++ x++ z++?
> i++  for row normal
> x++  for case WHEN column2 = 100
> z++  for case WHEN column3 = y  ?
>

Why are you asking SQL questions in PHP newsgroups?  You should be 
asking in a newsgroup for SQL or, more preferably, one for the database 
you're using.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web