CODEWITHSUNDEEP
sqldb2
Mike
Mike

Reputation: 401

How to modify query to walk entire table rather than a single

I wrote several SQL queries and executed them against my table. Each individual query worked. I kept adding functionality until I got a really ugly working query. The problem is that I have to manually change a value every time I want to use it. Can you assist in making this query automatic rather than “manual”?

I am working with DB2.

Table below shows customers (cid) from 1 to 3. 'club' is a book seller, and 'qnty' is the number of books the customer bought from each 'club'. The full table has 45 customers. enter image description here

Image below shows all the table elements for the first 3 users (cid=1 OR cid=2 OR cid=3). The final purpose of all my queries (once combined) is it to find the single 'club' with the largest 'qnty' for each 'cid'. So for 'cid =1' the 'club' is Readers Digest with 'qnty' of 3. For 'cid=2' the 'club' is YRB Gold with 'qnty' of 5. On and on until cid 45 is reached.

enter image description here

To give you a background on what I did here are my queries:

(Query 1-starting point for cid=1)

SELECT * FROM yrb_purchase WHERE cid=1 enter image description here

(Query 2 - find the 'club' with the highest 'qnty' for cid=1)

SELECT *
FROM
(SELECT club,
        sum(qnty) AS t_qnty
FROM yrb_purchase
WHERE cid=1
GROUP BY club)results
ORDER BY t_qnty DESC

enter image description here

(Query 3 – combine the record from the above query with it’s cid) 

SELECT cid,
    temp.club,
    temp.t_qnty
FROM yrb_purchase AS p,

(SELECT *
FROM
    (SELECT club,
            sum(qnty) AS t_qnty
    FROM yrb_purchase
    WHERE cid=1
    GROUP BY club)results
ORDER BY t_qnty DESC FETCH FIRST 1 ROWS ONLY) AS TEMP
WHERE p.cid=1
AND p.club=temp.club

enter image description here

(Query 4) make sure there is only one record for cid=1

SELECT cid,
    temp.club,
    temp.t_qnty
FROM yrb_purchase AS p,

(SELECT *
FROM
    (SELECT club,
            sum(qnty) AS t_qnty
    FROM yrb_purchase
    WHERE cid=1
    GROUP BY club)results
ORDER BY t_qnty DESC FETCH FIRST 1 ROWS ONLY) AS TEMP
WHERE p.cid=1
AND p.club=temp.club FETCH FIRST ROWS ONLY

enter image description here

To get the 'club' with the highest 'qnty' for customer 2, I would simply change the text cid=1 to cid=2 in the last query above. My query seems to always produce the correct results. My question is, how do I modify my query to get the results for all 'cid's from 1 to 45 in a single table? How do I get a table with all the cid values along with the club which sold that cid the most books, and how many books were sold within one tablei? Please keep in mind I am hoping you can modify my query as opposed to you providing a better query.

If you decide that my query is way too ugly (I agree with you) and choose to provide another query, please be aware that I just started learning SQL and may not be able to understand your query. You should be aware that I already asked this question: For common elements, how to find the value based on two columns? SQL but I was not able to make the answer work (due to my SQL limitations - not because the answer wasn't good); and in the absence of a working answer I could not reverse engineer it to understand how it works. Thanks in advance

****************************EDIT #1******************************************* The results of the answer is:

enter image description here

Upvotes: 2

Views: 243

Answers (1)

JNevill
JNevill

Reputation: 50034

You could use OLAP/Window Functions to achieve this:

SELECT
    cid,
    club,
    qnty
FROM
    (
        SELECT
            cid,
            club,
            qnty,
            ROW_NUMBER() OVER (PARTITION BY cid order by qnty desc) as cid_club_rank
        FROM
            (
                SELECT 
                    cid,
                    club, 
                    sum(qnty) as qnty   
                FROM yrb_purchase 
                GROUP BY cid, club
            ) as sub1
    ) as sub2
WHERE cid_club_rank = 1

The inner most statement (sub1) just grabs a total quantity for each cid/club combination. The second inner most statement (sub2) creates a row_number for each cid/club combination ordering by the quantity (top down). Then the outer most query chooses only records where that row_number() is 1.

Upvotes: 1

Related Questions