-2

Working on vertica database currently,faced some error,have no idea for the error so need some helps here :) below are my query and expected output:

SELECT 
  country
  , merchant
  , DISTINCT(merchant)
  , COUNT(*) as 'Total Transaction'
  , Max(price) as 'Max_Charge' 
FROM transaction_table
WHERE  ("action")='CHARGE' and action_status='COMPLETED'
GROUP by(msisdn)

my table and expected output

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
KY LIM
  • 1
  • 1

1 Answers1

0

The query does not seem to make a lot of sense, and from what I understood, it has really basic SQL shortcomings, that's why I voted you down. See the explanation below; try to follow the suggestions; and finally edit your question once you've tried something along the lines below.

Looks like going back to the documentation of (any, not just Vertica) SQL could help you a lot:

  1. The DISTINCT keyword is only "legal" directly after SELECT or in COUNT(DISTINCT <expression>)
  2. In a GROUP BY query, the columns in the SELECT list are either columns that will be repeated in the GROUP BY clause, or they are aggregate functions - like your MAX() and your COUNT() . GROUP BY (msisdn) when msisdn is not in the SELECT list won't help at all.

Hope these hints help ---

Good luck

Marco the Sane

marcothesane
  • 6,192
  • 1
  • 11
  • 21