Select row in which the sum of the times the same value came up is less than X

Question

this one been puzzling me for a couple of searching hours.

So I have a campaign table and a vendor Table. The vendor might have several campaigns. I want to select all campaigns if the vendor has enough credits.

Problem is I don't know how many campaigns are going to be selected from the same vendor which means that the vendor might still have credits for two campaigns but not for the rest of them.

Example

tblvendors

+---------+------------+---------------+
|vendorId | vendorName | vendorCredits |
+---------+------------+---------------+
|    1    |      a     |      5        |
|    2    |      b     |      100      |
+---------+------------+---------------+

tblproducts

+-----------+---------------+------------+
| productId |  productName  |  vendorId  |
+-----------+---------------+------------+
|     1     |       c       |      1     |
|     2     |       e       |      2     |
|     3     |       f       |      1     |
|     4     |       g       |      1     |
|     5     |       h       |      1     |
+-----------+---------------+------------+

tblcampaigns

+------------+---------------+------------+
| campaignId |   productId   |  vendorId  |
+------------+---------------+------------+
|      1     |       1       |      1     |
|      2     |       2       |      2     |
|      3     |       3       |      1     |
|      4     |       4       |      1     |
|      5     |       5       |      1     |
+------------+---------------+------------+

Now considering that everytime a row is selected the vendor looses 2 credits since vendor 'a' only has 5 credits left only campaigns 1 2 and 3 should be returned.

My current Query is this:

SET @maxCampaignId = (SELECT MAX(campaignId) FROM tblCampaigns);
SELECT
    @maxCampaignId,
    t0.campaignId,
    t0.productId,
    productName,
    productDescription,
    productImage,
    (CASE WHEN campaignId > (SELECT configValue FROM tblconfiguration WHERE configKey = 'lastHomeCampaignId')
        THEN campaignId ELSE campaignId + @maxCampaignId END) AS orderField
FROM tblcampaigns AS t0
    INNER JOIN tblproducts AS t1 ON t0.productId = t1.productId
        INNER JOIN tblvendors AS t2 ON t1.vendorId = t2.vendorId
WHERE
    campaignType = 'homeFeature' AND
    t0.isActive = 1 AND
    t2.vendorCredits > (SELECT configValue FROM tblconfiguration WHERE configKey = 'campaignHomeFeatureCost' LIMIT 1)
ORDER BY orderField ASC
LIMIT 4

The problem as you can see is int the line that compares the vendorCredits. Obviously as is the query selects more campaigns than the vendor can afford.

I wanted to avoid doing this in PHP as I think it should be possible to do this straight out of the database.

Hi, it depends. If i cannot do it in MySql than it will have to be done in PHP. Although I can easily do this in PHP I wanted to know how to in MySql. — helderjsm, Oct 24 '12 at 12:07

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

Check this post, it may help - group by and having clauses. I'll try to do some test later

Using COUNT(*) in the WHERE clause

UPDATE:

select t2.vendorId, vendorCredits from tblcampaigns AS t0 JOIN tblproducts AS t1 ON t0.productId = t1.productId JOIN tblvendors AS t2 ON t1.vendorId = t2.vendorId group by t2.vendorId having t2.vendorCredits = count(t2.vendorId)

If I correctly understood the question: This query will select all vendors having more campains than credits.

edited May 23 '17 at 11:48

Community

1
1

answered Oct 24 '12 at 12:15

Reflective

3,854
1
13
25

Hi, Thanks for the reply but COUNT will group my results and I can't group it since I need those results. – helderjsm Oct 24 '12 at 12:53
you can use this select in where clause of a new select selecting all the things you need – Reflective Oct 24 '12 at 12:59
If you mean a subquery in the where clause I've got the feeling that it would become clumsy code to maintain. I was expecting more something in line with this: http://stackoverflow.com/questions/10421807/how-do-i-limit-the-number-of-rows-per-field-value-in-sql – helderjsm Oct 24 '12 at 13:18

score 0 · Accepted Answer · edited May 23 '17 at 11:48

Ok found it.

Thanks to this post: How do I limit the number of rows per field value in SQL?

What I did was Selecting the rows I wanted in the order I wanted as a subquery and its respective row number so that I could reorder it back in the end.

Then I made a second subquery ordered by the vendorId so that I could count the number of times it turned up and returning the row_count to the main query.

Finally in the main query I reordered it back to the row number in the deepest subquery but now I have the value I wanted to compare which is the value of credits per row * the current row number for a particular vendor.

Anyways maybe the code is cleared and here it goes:

SET @creditsCost = (SELECT configValue FROM tblconfiguration WHERE configKey = 'campaignHomeFeatureCost' LIMIT 1);
SET @maxCampaignId = (SELECT MAX(campaignId) FROM tblCampaigns);
SET @curRow = 0;
SELECT * FROM
(
    SELECT *,
        @num := if(@first_column = vendorId, @num:= @num + 1, 1) as row_num,
        @first_column:=vendorId as c
    FROM
        (SELECT
            @curRow := @curRow + 1 AS row_number,
            @maxCampaignId,
            t0.campaignId,
            t0.productId,
            t2.vendorId,
            t2.vendorCredits,
            productName,
            productDescription,
            productImage,
            (CASE WHEN campaignId > (SELECT configValue FROM tblconfiguration WHERE configKey = 'lastHomeCampaignId')
                THEN campaignId ELSE campaignId + @maxCampaignId END) AS orderField
        FROM tblcampaigns AS t0
            INNER JOIN tblproducts AS t1 ON t0.productId = t1.productId
                INNER JOIN tblvendors AS t2 ON t1.vendorId = t2.vendorId
        WHERE
            campaignType = 'homeFeature' AND
            t0.isActive = 1
        ORDER BY orderField ASC) AS filteredCampaigns
    ORDER BY vendorId
) AS creditAllowedCampaigns
WHERE
    row_num * @creditsCost <= vendorCredits
ORDER BY row_number

Anyhow I still appreciate Who took the time to answer and try to help, and will be listening to future comments since I think this is not the best way performance wise.

Select row in which the sum of the times the same value came up is less than X

2 Answers2