2. PHPRunner General questions
  3. How to.. Mounth mysql query
This topic is locked

How to.. Mounth mysql query

 		3/3/2009 9:46:17 AM
PHPRunner General questions
S
 sickacid author

Hi,

i'm not very well in mysql query

i've a table with this fields
DATE ID_PRODUCT LITER
i want to obtain a monthly report whit sum of liter for every ID_PRODUCT
for example
JAN/08 XXX01 56

JAN/08 XXX02 78

FEB/09 XXx01 1200

.....
IT IS POSSIBLE? Thank's
Sergey Kornilov admin 3/3/2009

I recommend to create a report in PHPRunner groupping data by DATE field and choosing Month as an interval.
S
 		sickacid author 3/4/2009

I recommend to create a report in PHPRunner groupping data by DATE field and choosing Month as an interval.


Hi.. i've do it... with this mysql query
SELECT

tabellaspese.Litri,

tabellaspese.`Data`,

anagraficaautomezzi.Targa

FROM tabellaspese

LEFT OUTER JOIN anagraficaautomezzi ON tabellaspese.IdTarga = anagraficaautomezzi.ID

WHERE tabellaspese.TipoSpesa ='CARBURANTI'

GROUP BY tabellaspese.Litri, tabellaspese.`Data`, anagraficaautomezzi.Targa, tabellaspese.TipoSpesa

ORDER BY tabellaspese.`Data`, anagraficaautomezzi.Targa
and that's work, this is the query results
LITER DATA TARGA TYPE OF..
102.000 2008-04-21 CE042FS CARBURANTI

82.320 2008-04-21 CL438LP CARBURANTI

100.490 2008-04-21 CL439LP CARBURANTI

59.210 2008-04-21 CT657ZN CARBURANTI

105.360 2008-04-21 CT865ZN CARBURANTI

136.580 2008-04-21 DJ580ZK CARBURANTI

82.110 2008-04-22 CL438LP CARBURANTI

46.140 2008-04-22 CS637NH CARBURANTI

123.010 2008-04-22 CT738ZN CARBURANTI

61.750 2008-04-22 CT738ZN CARBURANTI

180.150 2008-04-22 CY860KM CARBURANTI

122.010 2008-04-22 DH275DZ CARBURANTI

32.340 2008-04-23 CL438LP CARBURANTI

98.660 2008-04-23 CT657ZN CARBURANTI

100.220 2008-04-23 DH275DZ CARBURANTI

221.900 2008-05-05 DJ580ZK CARBURANTI

47.500 2008-05-06 CS637NH CARBURANTI

86.940 2008-05-06 CT738ZN CARBURANTI

98.000 2008-05-06 DH275DZ CARBURANTI

97.220 2008-05-07 CE042FS CARBURANTI

119.450 2008-05-07 CL438LP CARBURANTI

97.270 2008-05-07 CT657ZN CARBURANTI

122.120 2008-05-07 CT865ZN CARBURANTI

200.000 2008-05-07 CY860KM CARBURANTI

120.000 2008-05-07 DH275DZ CARBURANTI

96.520 2008-05-08 CJ099ME CARBURANTI
I've put data in field grupping with interval as month, but

the results is like the query, i want to extrat a mounth summary

for example:
april/08

DH275DZ sum of this month

CT738ZN sum of this month

may/08

DH275DZ sum of this month

CT738ZN sum of this month
.....
J
 		Jane 3/4/2009

Simone,
I suppose you need to sum Litri field in the SQL query:

SELECT

sum(tabellaspese.Litri),

tabellaspese.`Data`,

anagraficaautomezzi.Targa

FROM tabellaspese

LEFT OUTER JOIN anagraficaautomezzi ON tabellaspese.IdTarga = anagraficaautomezzi.ID

WHERE tabellaspese.TipoSpesa ='CARBURANTI'

GROUP BY tabellaspese.`Data`, anagraficaautomezzi.Targa, tabellaspese.TipoSpesa

ORDER BY tabellaspese.`Data`, anagraficaautomezzi.Targa
S
 		sickacid author 3/4/2009

Simone,

I suppose you need to sum Litri field in the SQL query:


...not..

don't work...

i want to do this...

for example
JUNE/08

TARGA GROUP LITER for this month SUM
(i've 15 targa with many lines of liter pro month)
JULY/08

TARGA GROUP LITER for this month SUM

etc..
J
 		Jane 3/5/2009

Simone,
Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages and I'll try to help you.
S
 		sickacid author 3/6/2009

Simone,

Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages and I'll try to help you.


i've do this query
SELECT

YEAR( `Data` ),

MONTH( `Data` ),

`tabellaspese`.

`StazioneServizio`,

`anagraficaautomezzi`.`Targa`,

SUM( `tabellaspese`.`Litri` )

FROM `anagraficaautomezzi`, `tabellaspese`

WHERE `anagraficaautomezzi`.`ID` = `tabellaspese`.`IdTarga` AND `tabellaspese`.`TipoSpesa` = 'CARBURANTI' GROUP BY YEAR( `Data` ), MONTH( `Data` ), `tabellaspese`.`IdTarga` ORDER BY YEAR( `Data` ) ASC, MONTH( `Data` ) ASC, `anagraficaautomezzi`.`Targa` ASC, `tabellaspese`.`Data` ASC
That's works.. in openoffice but now i try to put it in phprunner.. thank's!