|
Home > Archive > SQL Anywhere database > July 2005 > Return only top sales amount query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Return only top sales amount query
|
|
| Wayne 2005-06-21, 11:23 am |
| Using ASA 9.01
I have a table: with these columns:
Store, SaleDate, Ticket, SeqNumber, Employee, SaleAmount.
Primary key is Store, SaleDate, Ticket, SeqNumber.
There can be multiple records per Store, SaleDate, Ticket.
I need to return for each Store, the SaleDate/Ticket with the highest
NetSale sum. So far I have this working as below but can not reduce it to
just the highest Ticket per Store, it returns all tickets sorted in
descending sequence. Any ideas?
select
Store, SaleDate, Ticket,
max(Employee) as Employee,
sum(NetSales) as NetSales
from Sales
where
SaleDate between '20050101' and '20050630'
group by
Store, SaleDate, Ticket
order by
Store, NetSales desc
Thanks, Wayne
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-06-21, 11:23 am |
| Use FIRST
select FIRST
Store, SaleDate, Ticket,
max(Employee) as Employee,
sum(NetSales) as NetSales
from Sales
where
SaleDate between '20050101' and '20050630'
group by
Store, SaleDate, Ticket
order by
Store, NetSales desc
See the following section of the docs :
ASA SQL User's Guide
Summarizing, Grouping, and Sorting Query Results
The ORDER BY clause: sorting query results
Explicitly limiting the number of rows returned by a query
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
"Wayne" <wloeppky400@shaw.ca> wrote in message news:42b82dad@forums
-2-dub...
> Using ASA 9.01
>
> I have a table: with these columns:
> Store, SaleDate, Ticket, SeqNumber, Employee, SaleAmount.
> Primary key is Store, SaleDate, Ticket, SeqNumber.
> There can be multiple records per Store, SaleDate, Ticket.
>
> I need to return for each Store, the SaleDate/Ticket with the highest
> NetSale sum. So far I have this working as below but can not reduce it to
> just the highest Ticket per Store, it returns all tickets sorted in
> descending sequence. Any ideas?
>
> select
> Store, SaleDate, Ticket,
> max(Employee) as Employee,
> sum(NetSales) as NetSales
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket
> order by
> Store, NetSales desc
>
> Thanks, Wayne
>
>
| |
| Wayne 2005-06-21, 11:23 am |
| That returns only one record for the entire query, or the first Store. I
need the first record for each Store. I suppose I can run the query for
each store.
Wayne
"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:42b83550$1@foru
ms-1-dub...
> Use FIRST
>
> select FIRST
> Store, SaleDate, Ticket,
> max(Employee) as Employee,
> sum(NetSales) as NetSales
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket
> order by
> Store, NetSales desc
>
>
> See the following section of the docs :
>
> ASA SQL User's Guide
> Summarizing, Grouping, and Sorting Query Results
> The ORDER BY clause: sorting query results
> Explicitly limiting the number of rows returned by a query
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Wayne" <wloeppky400@shaw.ca> wrote in message
> news:42b82dad@forums
-2-dub...
>
>
| |
| Paul Horan[TeamSybase] 2005-06-21, 1:23 pm |
| select
Store,
SaleDate,
Ticket,
Employee,
sum(NetSales) as SumNetSales
from Sales
where
SaleDate between '20050101' and '20050630'
group by
Store, SaleDate, Ticket, Employee
HAVING
SumNetSales =
(Select max( netsales )
from sales S2
where S2.store = SALES.store and
S2.SaleDate = SALES.SaleDate and
S2.Ticket = SALES.ticket )
order by
Store, SumNetSales desc
--
Paul Horan[TeamSybase]
"Wayne" <wloeppky400@shaw.ca> wrote in message news:42b82dad@forums
-2-dub...
> Using ASA 9.01
>
> I have a table: with these columns:
> Store, SaleDate, Ticket, SeqNumber, Employee, SaleAmount.
> Primary key is Store, SaleDate, Ticket, SeqNumber.
> There can be multiple records per Store, SaleDate, Ticket.
>
> I need to return for each Store, the SaleDate/Ticket with the highest NetSale sum. So far I have this working as
> below but can not reduce it to just the highest Ticket per Store, it returns all tickets sorted in descending
> sequence. Any ideas?
>
> select
> Store, SaleDate, Ticket,
> max(Employee) as Employee,
> sum(NetSales) as NetSales
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket
> order by
> Store, NetSales desc
>
> Thanks, Wayne
>
| |
| Glenn Paulley 2005-06-21, 8:23 pm |
| "Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in
news:42b84827$1@foru
ms-2-dub:
> select
> Store,
> SaleDate,
> Ticket,
> Employee,
> sum(NetSales) as SumNetSales
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket, Employee
> HAVING
> SumNetSales =
> (Select max( netsales )
> from sales S2
> where S2.store = SALES.store and
> S2.SaleDate = SALES.SaleDate and
> S2.Ticket = SALES.ticket )
> order by
> Store, SumNetSales desc
>
Trust Paul to use a correlated subquery ;-)
Instead, since you're using 901, use a WINDOW function - it will be much
more efficient than using a subquery. I believe this query will return
the result you want:
select * from ( select
Store, SaleDate, Ticket, employee,
sum(SaleAmount) as NetSales,
MAX (SUM(SaleAmount)) OVER (PARTITION BY
"Store" ,SaleDate,Ticket,emp
loyee) AS Max_sale_amt
from Sales
where SaleDate between '20050101' and '20050630'
group by store, SaleDate, Ticket, employee, SeqNumber ) as DT
where NetSales = Max_sale_amt
order by store, Max_sale_amt desc
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
| |
| Breck Carter [TeamSybase] 2005-06-21, 8:23 pm |
| OK, while we're ZINGING a fellow TeamSybase member, let's draw
everyone's attention to all the DETAILED documentation of the
wonderful new OLAP features... oh, wait, there's hardly any ;)
Breck Springing To Paul's Defense
PS you won't find any in the book, either :)
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Andrew Giulinn 2005-06-21, 8:23 pm |
| Yes - ask my boss about the lack of detailed documentation for the wonderful
new OLAP features! Seems like they do good things, but working out how to
make them do those good things is quite an achievement.
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:3r2hb11usiksu39
0uanuvi9limdhevrshs@
4ax.com...
> OK, while we're ZINGING a fellow TeamSybase member, let's draw
> everyone's attention to all the DETAILED documentation of the
> wonderful new OLAP features... oh, wait, there's hardly any ;)
>
> Breck Springing To Paul's Defense
>
> PS you won't find any in the book, either :)
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Paul Horan[TeamSybase] 2005-06-22, 3:23 am |
| "Glenn Paulley" <paulley@ianywhere.com> wrote in message news:Xns967CB33E2903
0paulleyianywherecom
@10.22.241.106...
>
> Trust Paul to use a correlated subquery ;-)
>
You think that's bad? You should witness the carnage in Lowell's wake since he discovered derived tables... <G>
-P-
| |
| David Kerber 2005-06-22, 7:23 am |
| In article < 3r2hb11usiksu390uanu
vi9limdhevrshs@4ax.com>,
NOSPAM__bcarter@risi
ngroad.com says...
> OK, while we're ZINGING a fellow TeamSybase member, let's draw
> everyone's attention to all the DETAILED documentation of the
> wonderful new OLAP features... oh, wait, there's hardly any ;)
>
> Breck Springing To Paul's Defense
>
> PS you won't find any in the book, either :)
Yes, I noticed that!
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
| Breck Carter [TeamSybase] 2005-06-22, 9:23 am |
| On 22 Jun 2005 05:18:12 -0700, David Kerber
< ns_dkerber@ns_wraenv
iro.com> wrote:
>In article < 3r2hb11usiksu390uanu
vi9limdhevrshs@4ax.com>,
> NOSPAM__bcarter@risi
ngroad.com says...
>
>Yes, I noticed that!
I started writing the book for version 9.0.0, and decided that it
would *never* get finished if 9.0.1 stuff crept in... the folks at
iAnywhere add features faster than I can figure them out.
Anyway, that's my excuse and I'm sticking to it :)
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Wayne 2005-06-22, 11:23 am |
| Thanks for the examples, will give these and try and see how they work out.
Will report back which works best.
Wayne
"Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in message
news:42b84827$1@foru
ms-2-dub...
> select
> Store,
> SaleDate,
> Ticket,
> Employee,
> sum(NetSales) as SumNetSales
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket, Employee
> HAVING
> SumNetSales =
> (Select max( netsales )
> from sales S2
> where S2.store = SALES.store and
> S2.SaleDate = SALES.SaleDate and
> S2.Ticket = SALES.ticket )
> order by
> Store, SumNetSales desc
>
> --
> Paul Horan[TeamSybase]
>
> "Wayne" <wloeppky400@shaw.ca> wrote in message
> news:42b82dad@forums
-2-dub...
>
>
| |
| Wayne 2005-06-22, 11:23 am |
| Tried out these two techniques by didn't seem to get the answer I need.
Maybe a data example will help explain what I need in a better way:
Store SaleDate Ticket SeqNumber Employee SaleAmount
001 2005/01/01 1 1 100 20
001 2005/01/01 1 2 100 30
001 2005/01/04 2 1 101 40
001 2005/01/04 3 1 102 50
001 2005/01/04 3 2 102 50
002 2005/01/01 1 1 200 50
002 2005/01/04 2 1 200 40
002 2005/01/04 2 2 200 70
002 2005/01/04 3 1 201 30
002 2005/01/04 4 1 202 50
From this dataset I would like to get back as a result set, the one ticket
per store with the highest total sales in the selected data range:
Store SaleDate Ticket Employee SaleAmount
001 2005/01/04 3 102 100
002 2005/01/04 2 200 110
Wayne
"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns967CB33E2903
0paulleyianywherecom
@10.22.241.106...
> "Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in
> news:42b84827$1@foru
ms-2-dub:
>
>
> Trust Paul to use a correlated subquery ;-)
>
> Instead, since you're using 901, use a WINDOW function - it will be much
> more efficient than using a subquery. I believe this query will return
> the result you want:
>
> select * from ( select
> Store, SaleDate, Ticket, employee,
> sum(SaleAmount) as NetSales,
> MAX (SUM(SaleAmount)) OVER (PARTITION BY
> "Store" ,SaleDate,Ticket,emp
loyee) AS Max_sale_amt
> from Sales
> where SaleDate between '20050101' and '20050630'
> group by store, SaleDate, Ticket, employee, SeqNumber ) as DT
> where NetSales = Max_sale_amt
> order by store, Max_sale_amt desc
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
| |
| Ani Nica 2005-06-24, 8:23 pm |
| This is the query you need (I assume the table 'Sales' is defined as
create table Sales(Store int, SaleDate date, Ticket int, SeqNumber int,
Employee int, SaleAmount int) ):
select
dt_Store as Store, dt_SaleDate as SaleDate, dt_Ticket as Ticket, dt_Employee
as Employee, dt_SUM as SumNetSales
from (select
Store, SaleDate, Ticket, Employee , SUM(SaleAmount) as S,
max(S) over (Partition By (Store) ) as M
from Sales
where
SaleDate between '20050101' and '20050630'
group by
Store, SaleDate, Ticket, Employee ) as dt (dt_Store, dt_SaleDate,
dt_Ticket, dt_Employee , dt_SUM, dt_Max)
where dt_SUM = dt_Max
order by
dt_Store, SumNetSales desc
--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Wayne" <wloeppky400@shaw.ca> wrote in message
news:42b98ed3$1@foru
ms-1-dub...
> Tried out these two techniques by didn't seem to get the answer I need.
> Maybe a data example will help explain what I need in a better way:
>
> Store SaleDate Ticket SeqNumber Employee SaleAmount
> 001 2005/01/01 1 1 100 20
> 001 2005/01/01 1 2 100 30
> 001 2005/01/04 2 1 101 40
> 001 2005/01/04 3 1 102 50
> 001 2005/01/04 3 2 102 50
>
> 002 2005/01/01 1 1 200 50
> 002 2005/01/04 2 1 200 40
> 002 2005/01/04 2 2 200 70
> 002 2005/01/04 3 1 201 30
> 002 2005/01/04 4 1 202 50
>
> From this dataset I would like to get back as a result set, the one ticket
> per store with the highest total sales in the selected data range:
>
> Store SaleDate Ticket Employee SaleAmount
> 001 2005/01/04 3 102 100
> 002 2005/01/04 2 200 110
>
> Wayne
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns967CB33E2903
0paulleyianywherecom
@10.22.241.106...
>
>
| |
| Wayne 2005-07-11, 11:23 am |
| Hi Ani,
Thanks so much, that does exactly what I wanted.
Wayne
"Ani Nica" <ani@sybase> wrote in message news:42bc71df$1@foru
ms-1-dub...
> This is the query you need (I assume the table 'Sales' is defined as
> create table Sales(Store int, SaleDate date, Ticket int, SeqNumber
> int,
> Employee int, SaleAmount int) ):
>
>
> select
> dt_Store as Store, dt_SaleDate as SaleDate, dt_Ticket as Ticket,
> dt_Employee
> as Employee, dt_SUM as SumNetSales
> from (select
> Store, SaleDate, Ticket, Employee , SUM(SaleAmount) as S,
> max(S) over (Partition By (Store) ) as M
> from Sales
> where
> SaleDate between '20050101' and '20050630'
> group by
> Store, SaleDate, Ticket, Employee ) as dt (dt_Store, dt_SaleDate,
> dt_Ticket, dt_Employee , dt_SUM, dt_Max)
> where dt_SUM = dt_Max
> order by
> dt_Store, SumNetSales desc
>
> --
> Ani Nica
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
> "Wayne" <wloeppky400@shaw.ca> wrote in message
> news:42b98ed3$1@foru
ms-1-dub...
>
>
|
|
|
|
|