CODEWITHSUNDEEP
mysql
Morfidon
Morfidon

Reputation: 1528

MySQL - How to sum up first occurrences from many different products

I have a big view called: how_many_per_month

name_of_product | how_many_bought | year | month
p1                20               2012    1   
p2                7                2012    1
p1                10               2012    2
p2                5                2012    2
p1                3                2012    3
p2                20               2012    3
p3                66               2012    3

How to write MySQL query in order to get only first few occurences of product p1, p2, p3 at once?

To get it one by one for first 3 months I can write:

SELECT name_of_product , sum(how_many_bought) FROM 
(SELECT name_of_product, how_many_bought FROM `how_many_per_month` 
WHERE name_of_product= 'p1' LIMIT 3) t

How to do it to all possible products at once so my result for taking only first month is like:

p1 20
p2 7
p3 66

For two months:

p1 30
p2 12
p3 66

The problem is that some products are published in different months and I have to make statistic how many of total of them are sold in first month, first 3 months, 6 months, 1 year divided by total.

Upvotes: 1

Views: 250

Answers (3)

tftd
tftd

Reputation: 17062

I'm not quite sure what you're trying to achieve as the description of your question is a bit unclear. From what I've read so far, I understand you want to show the total of how many ITEM_X, ITEM_Y, ITEM_Z were sold for the past 1,3,6 months.

Based on the data you've provided, I've created this sqlfiddle that sums all results and groups them by item. This is the query:

SELECT 
  name_of_product,
  sum(how_many_bought) as how_many_bought
FROM how_many_per_month
WHERE year = 2012
AND month BETWEEN 1 AND 3
GROUP BY name_of_product

-- NOTE: Not specifying an year will result in including all "months" 
         which are between the values 1 and 3 for all years. Remove it 
         in case you need that effect.

In the example above the database will sum all sold items between months 1 and 3 (including) for 2012. When you execute this query in your application just change the range in the BETWEEN X AND X and you'll be good to go.

Additional tip:
Avoid using sub-queries or try using the as a last resort method (in case there's simply no other way to do it). They are significantly slower than normal and even join queries. Usually sub-queries can be transformed into a join query.

Upvotes: 1

Mateusz Nowak
Mateusz Nowak

Reputation: 4121

Example using union

select 
  name_of_product, 
  sum(how_many_bought) as bought,
  "first month" as period
from how_many_per_month
where month = 1
group by name_of_product

union

select 
  name_of_product, 
  sum(how_many_bought) as bought,
  "first 2 month" as period
from how_many_per_month
where month <= 2
group by name_of_product

union

select 
  name_of_product, 
  sum(how_many_bought) as bought,
  "first 6 month" as period
from how_many_per_month
where month <= 6
group by name_of_product

union

select 
  name_of_product, 
  sum(how_many_bought) as bought,
  "first 12 month" as period
from how_many_per_month
where month <= 12
group by name_of_product

Demo: http://www.sqlfiddle.com/#!2/788ea/11

Results are different a little bit from your expectation. Are you sure that you write them properly? If you need to gain more speed in query time you can use group by case as I've already said.

Upvotes: 1

Deep
Deep

Reputation: 2512

SELECT
    hmpm.name_of_product , SUM(hmpm.how_many_bought)
  FROM (
    SELECT name_of_product
      FROM how_many_per_month
      /* WHERE ... */
      /* ORDER BY ... */
  ) sub
  INNER JOIN how_many_per_month hmpm
    ON hmpm.name_of_product = sub.name_of_product
  GROUP BY hmpm.name_of_product
  /* LIMIT ... */

MySQL not support LIMIT in subquery, but you need ordering and condition. And why not have id_of_product field?

Upvotes: 0

Related Questions