adorni
Posts: 120
|
| Posted: 08/03/2012, 1:12 PM |
|
I have this table with this records:
1 GMX 1000
2 UXC 1000
3 GMX 300
4 AAA 1000
5 AAA 1000
I need to see in my grid:
GMX 700
UXC 1000
(the substract beetween 1000 and 300, two diferents columns). Not show AAA because the substract is 0 (1000 - 1000),
And count the rows in grid (two in this example).
How i can do my MYSQL syntax?
THanks!
|
 |
 |
Oper
Posts: 1195
|
| Posted: 08/03/2012, 1:25 PM |
|
if you are working with low data records
TABLE F1,VALUE,VALUE2
select F1,SUM(VALUE1-VALUE2) as TOTAL from new_table1
group by f1
having Total > 0
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
adorni
Posts: 120
|
| Posted: 08/03/2012, 5:02 PM |
|
Thanks! and for count total rows?????? (two in my example, not four)
Thanks again!!!
|
|
|
|
Oper
Posts: 1195
|
| Posted: 08/03/2012, 5:27 PM |
|
Same Deal if not too many records
Select count(*) as TCOUNT from (
select F1,SUM(VALUE1-VALUE2) as TOTAL from new_table1
group by f1
having Total > 0 ) as X;
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
adorni
Posts: 120
|
| Posted: 08/05/2012, 2:42 PM |
|
Why "as X;" in the final syntax?
THanks (sorry for my bad mysql)
|
|
|
|
adorni
Posts: 120
|
| Posted: 08/05/2012, 2:44 PM |
|
The syntax say error when i execute the php:
Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; LIMIT 0,51' at line 5
My syntax is:
SELECT COUNT(*) as TCOUNT
FROM (SELECT (MontoRecibo - Montodev) as Neto
FROM licitaciones
GROUP BY Contar
HAVING (Sum(MontoRecibo) - Sum(Montodev)) > 0) as x;
And work ehwn i click on CCS in "Review data" in Visual QUery Builder, but then i execute the exported php page not work.
Very thanks
|
|
|
|
Oper
Posts: 1195
|
| Posted: 08/05/2012, 3:46 PM |
|
dont add the ";" at the end cuase CCS will add the Limit Part.
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
adorni
Posts: 120
|
| Posted: 08/05/2012, 4:23 PM |
|
Great!!! Thanks!!!
And why "as x" in the syntax???
Thanks!
|
|
|
|
Oper
Posts: 1195
|
| Posted: 08/05/2012, 4:46 PM |
|
any "AS whatever" will work:
ex:
AS adorni
MySQL rule if you use a SubQuery as an CURSOR/RECORDSET data you have to give an alias name. (Even if you dont used)
like X.TCOUNT
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
