FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » sql how to have the total rows number in this case (this is correct question)
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
sql how to have the total rows number in this case (this is correct question) [message #182513] Fri, 09 August 2013 14:32 Go to next message
nawfer is currently offline  nawfer
Messages: 34
Registered: August 2011
Karma: 0
Member
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
Re: sql how to have the total rows number in this case (this is correct question) [message #182514 is a reply to message #182513] Fri, 09 August 2013 14:39 Go to previous messageGo to next message
nawfer is currently offline  nawfer
Messages: 34
Registered: August 2011
Karma: 0
Member
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 ?
Re: sql how to have the total rows number in this case (this is correct question) [message #182515 is a reply to message #182514] Fri, 09 August 2013 17:31 Go to previous message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
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(at)attglobal(dot)net
==================
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: sql how to have the total rows number in this case nawfer
Next Topic: Re: Counter reset or not? UPDATE
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 10 04:44:19 GMT 2024

Total time taken to generate the page: 0.02218 seconds