Wyświetlenie danych według sumy prowizji z danego miesiąca Opcje

[gucio1]

Cześć,
mam polecenie które wyświetla mi wybrane dane z tabeli "transaction" oraz "operation" na raz wedle miesiąca który podam.

W przykładzie pobieram dane dla sierpnia 2017.

<!--Geshi:478319:sql--><pre class="sql" style="font-family:monospace;"><div class="head">[SQL] [/list]<div class="foot">[SQL] <a href='./Pobierz-Plik-478320.html' target='_blank_t0'>O_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">UNION</span> <span class="kw1">SELECT</span> email<span class="sy0">,</span>nrOperacji<span class="sy0">,</span>dataOperacji<span class="sy0">,</span>nazwaProduktu<span class="sy0">,</span>liczbaPunktow<span class="sy0">,</span>prowizja <span class="kw1">FROM</span> operation <span class="kw1">WHERE</span> prowizja <span class="sy0">></span> 0 <span class="kw1">AND</span> dataOperacji <span class="sy0">></span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span></div>
[/list]<div class="foot">[SQL] [topic=0]O_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">UNION</span> <span class="kw1">SELECT</span> email<span class="sy0">,</span>SUM<span class="br0">(</span>prowizja<span class="br0">)</span> <span class="kw1">AS</span> suma <span class="kw1">FROM</span> operation <span class="kw1">WHERE</span> prowizja <span class="sy0">></span> 0 <span class="kw1">AND</span> dataOperacji <span class="sy0">></span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">ORDER</span> <span class="kw1">BY</span> suma <span class="kw1">DESC</span> <span class="kw1">LIMIT</span> <span class="nu0">5</span></div>
[/list]<div class="foot">[SQL] <a href='./Pobierz-Plik-478320.html' target='_blank, <a href='Plaintext-478320.html' target='_blank_t0'>O_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">UNION</span> <span class="kw1">SELECT</span> email<span class="sy0">,</span>SUM<span class="br0">(</span>prowizja<span class="br0">)</span> <span class="kw1">AS</span> suma <span class="kw1">FROM</span> operation <span class="kw1">WHERE</span> prowizja <span class="sy0">></span> 0 <span class="kw1">AND</span> dataOperacji <span class="sy0">></span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">ORDER</span> <span class="kw1">BY</span> suma <span class="kw1">DESC</span> <span class="kw1">LIMIT</span> <span class="nu0">5</span></div>
[/list]<div class="foot">[SQL] <a href='./Pobierz-Plik-478320.html' target='_blank_t0'>O_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">UNION</span> <span class="kw1">SELECT</span> email<span class="sy0">,</span>nrOperacji<span class="sy0">,</span>dataOperacji<span class="sy0">,</span>nazwaProduktu<span class="sy0">,</span>liczbaPunktow<span class="sy0">,</span>prowizja <span class="kw1">FROM</span> operation <span class="kw1">WHERE</span> prowizja <span class="sy0">></span> 0 <span class="kw1">AND</span> dataOperacji <span class="sy0">></span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span></div>
[/list]<div class="foot">[SQL] [topic=0]O_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">UNION</span> <span class="kw1">SELECT</span> email<span class="sy0">,</span>SUM<span class="br0">(</span>prowizja<span class="br0">)</span> <span class="kw1">AS</span> suma <span class="kw1">FROM</span> operation <span class="kw1">WHERE</span> prowizja <span class="sy0">></span> 0 <span class="kw1">AND</span> dataOperacji <span class="sy0">></span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-08'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">AND</span> dataOperacji <span class="sy0"><</span> STR_TO_DATE<span class="br0">(</span><span class="st0">'2017-09'</span><span class="sy0">,</span><span class="st0">'%Y-%m'</span><span class="br0">)</span> <span class="kw1">GROUP</span> <span class="kw1">BY</span> email <span class="kw1">ORDER</span> <span class="kw1">BY</span> suma <span class="kw1">DESC</span> <span class="kw1">LIMIT</span> <span class="nu0">5</span></div>
[/list]<div class="foot">[SQL] <a href='./Pobierz-Plik-478320.html' target='_blank, <a href='Plaintext-478320.html' target='_blank </div></pre><!--/Geshi:478320:sql-->

Ten post edytował gucio1 28.08.2017, 17:11:14