MAX SUM and using TOP in Views

Question

MAX SUM and using TOP in Views

SQL Server has the following tables:

COMMANDLINES

: ID_LINE - ID_COMMAND - ID_ARTICLE - QUANTITY
COMMAND

: ID_COMMAND - ID_CLIENT - PRICE - PRINTED
CLIENT

: ID_CLIENT - FULL_NAME - SSN - PH_NUM - MOBILE - USERNAME - PASSWORD
ARTICLE

: ID_ARTICLE - DES - NAME - PRICE - TYPE - CURRENT_QTT - MINIMUM_QTT
ID_COMMAND

from COMMANDLINES

linksCOMMAND.ID_COMMAND
ID_CLIENT

from COMMAND

linksCLIENT.ID_CLIENT
ID_ARTICLE

from COMMANDLINES

linksARTICLE.ID_ARTICLE

I need to create view

where I need to show all the COMMANDLINES

best customer (the one with the highest total PRICE

) and then I need to order them ID_COMMAND

in descending order AND ID_LINE

in ascending order.

Sample data:

COMMANDLINE

table:

COMMAND

table:

<code> COMMAND </code>
<br>
<script async src=
" data-src="/img/c81f6fe8f4d0f12afbabf51d1b440f4d.png" class=" lazyloaded" src="https://fooobar.com//img/c81f6fe8f4d0f12afbabf51d1b440f4d.png">

Only these 2 are needed to solve the problem. I added another one just for more information.

Output example:

To be honest, I'm not sure if both outputs should be "output" at the same time or if I need 2 VIEWS

for each output.

WHAT I DID WHAT I DID:

I went through everything I could find on StackOverflow MAX

SUM

, but unfortunately it didn't work for me in this case. I always seem to be doing something wrong.

I also figured out what to use ORDER BY

in VIEWS

you need to use in this case TOP

, but I don't know how to properly apply it when I need to select everything COMMANDLINES

. In one of my previous things, I used the following SELECT TOP

:

create view PRODUCTS_BY_TYPE
as
    select top (select count(*) from ARTICLE 
                where CURRENT_QTT > MINIMUM_QTT)*
    from 
        ARTICLE
    order by 
        TYPE

This allowed me to show all the PRODUCT

data where CURRENT_QTT

there was more than the minimum ordering it by type, but I can't figure out all my life how to apply this to my current situation.

I could start with something like this:

create view THE_BEST
as
    select COMMANDLINE.*
    from COMMANDLINE

But then I don't know how to apply TOP

.

I realized that firstly, I need to see who is the best client SUM

- everything PRICE

under his ID, and then do it MAX

on SUM

all clients.

So far, the best I could think of is this:

create view THE_BEST
as
    select top (select count(*) 
                from (select max(max_price) 
                      from (select sum(PRICE) as max_price 
                            from COMMAND) COMMAND) COMMAND) COMMANDLINE.*
    from COMMANDLINE
    inner join COMMAND on COMMANDLINE.ID_COMMAND = COMMAND.ID_COMMAND
    order by COMMAND.ID_COMMAND desc, COMMANDLINE.ID_LINE asc

Unfortunately in is select count(*)

COMMAND

underlined in red (aka 3rd word COMMAND

) and it says COMMAND

there is no column for column 1 of the section .

EDIT:

I came up with something closer to what I want:

create view THE_BEST
    as
        select top (select count(*) 
                    from (select max(total_price) as MaxPrice 
                          from (select sum(PRICE) as total_price 
                                from COMMAND) COMMAND) COMMAND)*
        from COMMANDLINE
        order by ID_LINE asc

Still missing ordered by ID_COMMAND

and I only get 1 result in the output when it should be 2.

+3

sql sql-server tsql

DoombringerBG June 11. 17 at 16:07

source to share

2 answers

tgr · Answer 1 · 2017-06-12T15:17:24+0000

here is some code that hopefully shows you how you can use the top clause as well as a different approach to only show the "top" :-)

/* Creating Tables*/
CREATE TABLE ARTICLE (ID_ARTICLE int,DES varchar(10),NAME varchar(10),PRICE float,TYPE int,CURRENT_QTT int,MINIMUM_QTT int)
CREATE TABLE COMMANDLINES (ID_LINE int,ID_COMMAND int,ID_ARTICLE  int,QUANTITY int)
CREATE TABLE COMMAND (ID_COMMAND int, ID_CLIENT varchar(20), PRICE float, PRINTED int)
CREATE TABLE CLIENT (ID_CLIENT varchar(20), FULL_NAME varchar(50), SSN varchar(50), PH_NUM varchar(50), MOBILE varchar(50), USERNAME varchar(50), PASSWORD varchar(50))

INSERT INTO COMMANDLINES VALUES (1,1,10,20),(2,1,12,3),(3,1,2,21),(1,2,30,2),(2,2,21,5),(1,3,32,20),(2,3,21,2)
INSERT INTO COMMAND VALUES (1,'1695152D',1200,0),(2,'1695152D',500,0),(3,'2658492D',200,0)
INSERT INTO ARTICLE VALUES(1, 'A','AA',1300,0,10,5),(2,'B','BB',450,0,10,5),(30,'C','CC',1000,0,5,5),(21,'D','DD',1500,0,5,5),(32,'E','EE',1600,1,4,5),(3,'F','FF',210,2,15,5)
INSERT INTO CLIENT VALUES ('1695152D', 'DoombringerBG', 'A','123','321','asdf','asf'),('2658492D', 'tgr', 'A','123','321','asdf','asf')
GO
/* Your View-Problem*/
CREATE VIEW PRODUCTS_BY_TYPE AS 
SELECT TOP 100 PERCENT *
FROM ARTICLE
WHERE CURRENT_QTT > MINIMUM_QTT -- You really don't want >= ??
ORDER BY [Type]
-- why do you need your view with an ordered output? cant your query order the data?
GO

OUTPUT:

ID_ARTICLE   | DES   | NAME  | PRICE | TYPE | CURRENT_QTT  | MINIMUM_QTT
-------------+-------+-------+-------+------+--------------+-------------
1            | A     | AA    | 1300  | 0    | 10           | 5
2            | B     | BB    | 450   | 0    | 10           | 5
3            | F     | FF    | 210   | 2    | 15           | 5

Hope this is what you were looking for :-)

-- your top customers
SELECT cli.FULL_NAME, SUM(c.PRICE)
FROM COMMANDLINES as cl
INNER JOIN COMMAND as c
on cl.ID_COMMAND = c.ID_COMMAND
INNER JOIN CLIENT as cli
on cli.ID_CLIENT = c.ID_CLIENT
GROUP BY cli.FULL_NAME
ORDER BY SUM(c.PRICE) DESC -- highest value first


SELECT * 
FROM (
    -- your top customers with a rank
    SELECT cli.FULL_NAME, SUM(c.PRICE) as Price, ROW_NUMBER() OVER (ORDER BY SUM(c.PRICE) DESC) AS RowN
    FROM COMMANDLINES as cl
    INNER JOIN COMMAND as c
    on cl.ID_COMMAND = c.ID_COMMAND
    INNER JOIN CLIENT as cli
    on cli.ID_CLIENT = c.ID_CLIENT
    GROUP BY cli.FULL_NAME
) as a
-- only the best :-)
where RowN = 1 
--considerations: what if two customers have the same value?

Output:

FULL_NAME       |Price    | RowN
----------------+---------+-------
DoombringerBG   | 4600    | 1

TGR relationships

===== EDITED =====

The corrention syntax for your THE_BEST-View is:

create view THE_BEST AS
SELECT TOP (
        SELECT count(*) as cnt
        FROM (
            SELECT max(max_price) as max_price
            FROM (
                SELECT sum(PRICE) AS max_price
                FROM COMMAND
                ) COMMAND
            ) COMMAND
        ) 
        cl.*
FROM COMMANDLINES  as  cl
INNER JOIN COMMAND as c
    ON cl.ID_COMMAND = c.ID_COMMAND
ORDER BY c.ID_COMMAND DESC
    ,cl.ID_LINE ASC

Without OVER

-Clause:

SELECT TOP 1 * 
FROM (
    -- your top customers with a rank
    SELECT cli.FULL_NAME, SUM(c.PRICE) as Price
    FROM COMMANDLINES as cl
    INNER JOIN COMMAND as c
    on cl.ID_COMMAND = c.ID_COMMAND
    INNER JOIN CLIENT as cli
    on cli.ID_CLIENT = c.ID_CLIENT
    GROUP BY cli.FULL_NAME
) as a
-- only the best :-)
ORDER BY Price DESC

Your PRODUCT_BY_TYPE without PERCENT:

CREATE VIEW PRODUCTS_BY_TYPE AS 
SELECT TOP (select 
SUM(p.rows)
from sys.partitions as p
inner join sys.all_objects as ao
on p.object_id = ao.object_id
where ao.name = 'ARTICLE'
and ao.type = 'U')
*
FROM ARTICLE
WHERE CURRENT_QTT > MINIMUM_QTT -- You really don't want >= ??
ORDER BY [Type]
go

but to be honest, I would never use such a query in production ... I only posted this because you need it to study ...

Vladimir Baranov · Answer 2 · 2017-06-13T14:08:29+0000

It is likely that there is some misunderstanding between you and your teacher. You can technically have a clause ORDER BY

in the view definition, but it never guarantees any row order in a query that uses the view, for example SELECT ... FROM your_view

. Without ORDER BY

in the latter, the SELECT

order of the result set is undefined. The order of the rows returned to the client by the server is determined only by the final external ORDER BY

request, not by the ORDER BY

view definition.

The purpose of the definition TOP

in the view definition is to somehow limit the number of rows returned. Eg TOP (1)

. In this case, ORDER BY

specifies which rows are returned.

The presence TOP 100 PERCENT

in the view does nothing. It does not reduce the number of rows returned and does not guarantee any particular order of returned rows.

Having said all that, in your case you need to find one best customer, so it makes sense to use TOP (1)

in a subquery.

This request will return the ID of the best customer:

SELECT 
    TOP (1) 
    -- WITH TIES
    ID_CLIENT
FROM COMMAND
GROUP BY ID_CLIENT
ORDER BY SUM(PRICE) DESC

If there can be multiple customers with the same maximum total price and you want to return data that applies to all of them, not just one random customer, then use TOP WITH TIES

.

Finally, you need to return the rows that match the selected client (s):

create view THE_BEST
as
    SELECT
        COMMANDLINE.ID_LINE
        ,COMMANDLINE.ID_COMMAND
        ,COMMANDLINE.ID_ARTICLE
        ,COMMANDLINE.QUANTITY
    FROM
        COMMANDLINE
        INNER JOIN COMMAND ON COMMAND.ID_COMMAND = COMMANDLINE.ID_COMMAND
    WHERE
        COMMAND.ID_CLIENT IN
        (
            SELECT 
                TOP (1) 
                -- WITH TIES
                ID_CLIENT
            FROM COMMAND
            GROUP BY ID_CLIENT
            ORDER BY SUM(PRICE) DESC
        )
;

Here's how you can use the view:

SELECT
    ID_LINE
    ,ID_COMMAND
    ,ID_ARTICLE
    ,QUANTITY
FROM THE_BEST
ORDER BY ID_COMMAND DESC, ID_LINE ASC;

Note that ORDER BY ID_COMMAND DESC, ID_LINE ASC

must be in the actual request, not in the view definition.

MAX SUM and using TOP in Views

More articles: