to make a global means in a request

Question

my table SALES

  ID | NAMEPRODUCT | CATEGORY  | AMOUNT
    1 |  COMPUTER   |  IT       | 600
    2 |  T-SHIRT    |  CLOTHING |  25
    3 |  Doll       |  TOY      |  10
    4 |  KEYBORD    |  IT       |  30
    5 |  CAP        |  CLOTHING |  10
    3 |  TOY CAR    |  TOY      |  40

I would like to make this type of request :

SELECT SALES1.NAMEPRODUCT, 
SALES1.CATEGORY,
SUM(SALES1.AMOUNT)/( select SUM(AMOUNT) FROM SALES AS SALES2 WHERE SALES1.CATEGORY=SALES2.CATEGORY) as ratio
from SALES AS SALES1
GROUP BY 
SALES1.NAMEPRODUCT,SALES1.CATEGORY

   NAMEPRODUCT  | CATEGORY  | ratio
     COMPUTER   |  IT       | 0,95
      T-SHIRT   |  CLOTHING | 0,71
     Doll       |  TOY      | 0,20
    KEYBORD     |  IT       | 0,5
     CAP        |  CLOTHING | 0,29
     TOY CAR    |  TOY      | 0,80

OR

SELECT SALES1.NAMEPRODUCT, 
SALES1.CATEGORY,
SUM(SALES1.AMOUNT),
( select SUM(SALES2 .AMOUNT) FROM SALES2 WHERE SALES1.CATEGORY=SALES2.CATEGORY) AS TOTAL_AMOUNT_for_category
from SALES1
GROUP BY 
SALES1.NAMEPRODUCT,
SALES1.CATEGORY

 NAMEPRODUCT | CATEGORY  | AMOUNT |TOTAL_AMOUNT_for_category
  COMPUTER   |  IT       | 600    | 630
  T-SHIRT    |  CLOTHING |  25    |  35
  Doll       |  TOY      |  10    |  50
  KEYBORD    |  IT       |  30    | 630
  CAP        |  CLOTHING |  10    |  35
  TOY CAR    |  TOY      |  40    |  50

I use Cassandra database with Apache Hive connector I can't make this type of request with hive and cassandra Can someone help me ?

Sample data and desire results would help. Why doesn't your query work? — Gordon Linoff, Jan 27 '20 at 14:54

score 2 · Accepted Answer · answered Jan 27 '20 at 16:17

2

You seem to have only one table. If so, use window functions:

select nameproduct, category,
       sum(amount) as amount,
       sum(amount) * 1.0 / sum(sum(amount)) over (partition by category) as ratio
from sales1
group by nameproduct, category;

answered Jan 27 '20 at 16:17

Gordon Linoff

1,242,037
58
646
786

what is the opposite of partition by, to remove a dimension from count or sum? – broly21 Jan 28 '20 at 10:18
@broly21 . . . `partition by` is optional. You can remove it to sum over all the data. – Gordon Linoff Jan 28 '20 at 11:17

to make a global means in a request

1 Answers1