MySQL query using CONCAT

Question

I have two tables:

categories => Category_ID, Title, Description, Default_Points, Groups

transactions => Transaction_ID, Datetime, Giver_ID, Recipient_ID, Points, Category_ID, Reason

Teachers award points, choosing a category (like "Positive Attitude & Behaviour") and a reason (like "Excellent work today") which puts an entry into the transactions table.

A typical categories row may be:

INSERT INTO `categories` (`Category_ID`, `Title`, `Description`, `Default_Points`, `Groups`) VALUES
(17, 'Olympic Values', 'Please clearly state the correct Olympic Value that''s being used currently in the REASON box.', 5, '');

A typical transactions row may be:

INSERT INTO `transactions` (`Transaction_ID`, `Datetime`, `Giver_ID`, `Recipient_ID`, `Points`, `Category_ID`, `Reason`) VALUES
(50, '2011-09-07', 35023, 90236, 5, 17, 'Excellent work during PE');

What I'd like to try and do using MySQL is produce a list of total points (i.e. SUM(transactions.Points) for EACH category, with a few sample Reasons too.

I'd imagine this will have to use a CONCAT?

I need:

SUM(transactions.Points) per category
categories.title
5 unique transactions.reason per category

This might look like...

Points      Title                   Sample
14252       Olympic Values          Excellent work in PE!|Great display of friendship|Well done!
15532       Outstanding Effort      Amazing work!|Worked so hard|Great piece!

Is this possible?

Thanks in advance,

ChrisPatrick · Accepted Answer · 2012-03-08 14:34:29Z

1

It's GROUP_CONCAT you want.

You'll need to do something like this:

SELECT SUM(t.Points), 
    c.title, 
    SUBSTRING_INDEX(GROUP_CONCAT(transactions.reasons SEPERATOR '|'), '|', 5)
FROM transactions t JOIN categories c ON (t.Category_ID=c.Category_ID)
GROUP BY c.Category_ID

answered Mar 8, 2012 at 14:34

ChrisPatrick

9848 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

turbonerd Over a year ago

Hi @ChrisPatrick. This works nicely apart from the fact that the reasons being returned aren't unique. Generally when the transactions are entered into the database, it'll be in batches of 30 (class size) and as such the first 5 transactions will often be with the same "Reason". Is there anything you can add to make the reasons unique?

turbonerd Over a year ago

Ahh, got it! Will post it as an answer but give you the credit.

turbonerd · Accepted Answer · 2012-03-09 15:19:16Z

0

Thanks to @ChrisPatrick 's answer, this is the code I used:

SELECT
 SUM(t.Points) AS Total_Points, 
    c.Title, 
    SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT t.reason SEPARATOR '|'), '|', 5) AS Sample_Reasons
FROM
 transactions t JOIN categories c ON (t.Category_ID=c.Category_ID)
GROUP BY c.Category_ID
ORDER BY c.Title ASC

answered Mar 9, 2012 at 15:19

turbonerd

1,3165 gold badges31 silver badges73 bronze badges

Collectives™ on Stack Overflow

MySQL query using CONCAT

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related