SQL - I am trying to isolate or group each transaction by each cardholder and my code is below

Question

The code runs ok when i don't have the last line (GROUP BY ch.name). When I include the line I receive an error listed below - I am trying to get a query where I can identify and group many things such as grouping transactions by cardholder and grouping from 7:00 - 9:00 AM EST. Still new to coding so a bit frustrated. Any help is appreciated!

ERROR: column "t.date" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: SELECT ch.name, t.date, t.amount, t.card AS "Credit Card", t...
^
SQL state: 42803
Character: 17

My query:

SELECT 
    ch.name, t.date, t.amount, t.card AS "Credit Card", t.id_merchant,
    m.name AS "Merchan, mc.name AS "merchant category"
FROM
    transaction AS t
JOIN 
    credit_card AS cc ON (t.card = cc.card)
JOIN 
    card_holder AS ch ON (cc.cardholder_id = ch.id)
JOIN 
    merchant AS m ON (t.id_merchant = m.id)
JOIN 
    merchant_category AS mc ON (m.id_merchant_category = mc.id)
GROUP BY 
    ch.name;

You typically `GROUP BY` the same columns as you `SELECT`, except those who are arguments to set functions. — jarlh, Feb 07 '20 at 15:45
Show us some sample table data and the expected result - all as formatted text (not images). Take a look at [mcve] too. — jarlh, Feb 07 '20 at 15:47
You typically combine GROUP BY with set functions, to calculate values per group. — jarlh, Feb 07 '20 at 15:48

score 0 · Accepted Answer · edited Feb 07 '20 at 18:08

0

SELECT ch.name, t.date, t.amount, t.card AS "Credit Card", t.id_merchant, m.name AS "Merchan mc.name AS "merchant category" 
FROM transaction AS t 
JOIN credit_card AS cc ON (t.card = cc.card) 
JOIN card_holder AS ch ON (cc.cardholder_id = ch.id) 
JOIN merchant AS m ON (t.id_merchant = m.id) 
JOIN merchant_category AS mc ON (m.id_merchant_category = mc.id) 
GROUP BY (ch.name, t.date, t.amount, t.card, t.id_merchant, m.name, mc.name);

Try above.

edited Feb 07 '20 at 18:08

marc_s

732,580
175
1,330
1,459

answered Feb 07 '20 at 15:45

lion_pankaj

160
1
12

1

You mean SELECT DISTINCT? – jarlh Feb 07 '20 at 15:46
1

This answer makes no sense, why would you GROUP BY all the fields in the SELECT? – Nedret Recep Feb 07 '20 at 15:47
You need to specify all the columns in group by clause. – lion_pankaj Feb 07 '20 at 15:47
It makes sense only if you have aggregation, which is not the case here – Nedret Recep Feb 07 '20 at 15:48
group by is aggregate function. that's why you need to specify all the column in group by clause. – lion_pankaj Feb 07 '20 at 15:49
You are using group by, so this whole result set will be aggregate. – lion_pankaj Feb 07 '20 at 15:50
@jarlh Group by without aggregate is basically the same as distinct: https://stackoverflow.com/questions/20074562/group-by-without-aggregate-function however for readability distinct is the preferred method. – Dan Donoghue Feb 17 '20 at 00:01
@DanDonoghue, you can also do `SELECT ... UNION same SELECT`, to get distinct result. But I still do SELECT DISTINCT... (Sarcasm) – jarlh Feb 17 '20 at 07:39

SQL - I am trying to isolate or group each transaction by each cardholder and my code is below

1 Answers1