Group By not working in SQL Server

Question

CREATE VIEW [dbo].[Payment_Transaction_vw]
AS 
SELECT payment_trans_id,
       Student_Info.student_fname,
       Student_Info.student_lname,
       Student_Info.ID_Number,
       Trimester_Payment.deadline,
       Transaction_Info.trans_name,
       Payment_Transaction.amount,
       Payment_Transaction.date_paid
FROM [Payment_Transaction]
  INNER JOIN Student_Info 
    ON Payment_Transaction.student_info_id = Student_Info.student_info_id
  INNER JOIN Trimester_Payment 
    ON Payment_Transaction.trimester_id = Trimester_Payment.trimester_id
  INNER JOIN Transaction_Info 
    ON Payment_Transaction.trans_info_id = Transaction_Info.trans_info_id
GROUP BY ID_Number,trans_name;

That is my script to make a view in sql server in visual studio, I wanted to group the ID_Number & trans_name which have a repeating values in the table Payment_Transactions. I wanted that this ID_Number with the trans_name will only displayed once. I also want to sum up the amount paid for every ID_number with the same trans_name.

enter image description here

And what is not working? A side note, grouping by ID_Number AND trans_name, will give you repeating values of ID_Number if they have different names... — sarin, Jul 01 '15 at 10:15
`Message SQL00000 An error occurred while the batch was being executed.` I have this error. — Jen143Me, Jul 01 '15 at 10:16
Can you show the code that creates this error? For example are you writing another query that uses Payment_Transaction_vw? or are you just trying to run CREATE VIEW [dbo].[Payment_Transaction_vw] ? — sarin, Jul 01 '15 at 10:19
BTW, I hope your screenshot shows fake names .. Otherwise please blank them, because you are posting **personal information** here! — SQL Police, Jul 01 '15 at 10:22
I hope Mr Sad Tangs doesn't see this! And, equally importantly, that Bad People don't look in the revision history to get the old, actual names now that that SQL Police have gone bolding the joint up! — , Jul 01 '15 at 10:55

Jan 'splite' K. · Answer 1 · 2015-07-01T10:18:23.217

2

You have to aggregate columns that are not in group by clause.

As example, which one of date_paid (for payment_trans_id = 1 and 2) you want to return? 6/25/2015 or 5/6/2015? SQL server cant know, so you get multiple rows.

edited Jul 01 '15 at 10:18

answered Jul 01 '15 at 10:14

Jan 'splite' K.

1,667
2
27
34

1

@Jen143Me But you've said you want the ID returned once, so there is one row for each ID. If you've got multiple values in the other columns for each ID, where would you expect them to go? Sounds like you want a pivot or something which puts them in multiple columns... – Bridge Jul 01 '15 at 10:17
So you want all the `date_paid` and you got them - i dont see any problem :) – Jan 'splite' K. Jul 01 '15 at 10:18
My mistake, what im trying to say is the ID_Number together with the trans_name would display only once. – Jen143Me Jul 01 '15 at 10:25
yeah, but in your `select` part, you are asking for `date_paid` (as example! -- `payment_trans_id`, `student_fname`, `student_lname`, `deadline` and `amount` - same case) - and then, which one of them do you want? -- Mssql cant just throw away data you requested nor know which one you want. Look at excelent answer from @Mackan bellow. – Jan 'splite' K. Jul 01 '15 at 10:44

Mackan · Accepted Answer · 2015-07-07T10:57:16.190

2

When using group by you want to make sure that any unique value will be aggregated or consolidated so that it can be displayed in one row. As it is right now, payment_trans_id (and others) are still unique and since you chose to display these the group by cannot be done.

What do you want to do with payment_trans_id, date_paid, amount ... all other columns really?

Example using MAX(), MIN() and AVG():

SELECT 
  MAX(payment_trans_id) AS payment_trans_id,
  Transaction_Info.trans_name,
  Student_Info.ID_Number, 
  AVG(Payment_Transaction.amount) AS amount,
  MIN(Payment_Transaction.date_paid) AS date_paid
FROM [Payment_Transaction]
INNER JOIN Student_Info ON Payment_Transaction.student_info_id = Student_Info.student_info_id 
INNER JOIN Trimester_Payment ON Payment_Transaction.trimester_id = Trimester_Payment.trimester_id 
INNER JOIN Transaction_Info ON Payment_Transaction.trans_info_id = Transaction_Info.trans_info_id 
GROUP BY ID_Number, trans_name;

For support in EF, perhaps this will be sufficient (perhaps not):

SELECT 
  ISNULL(MAX(payment_trans_id),0) AS Id,
  Transaction_Info.trans_name,
  Student_Info.ID_Number, 
  AVG(Payment_Transaction.amount) AS amount,
  MIN(Payment_Transaction.date_paid) AS date_paid
FROM [Payment_Transaction]
INNER JOIN Student_Info ON Payment_Transaction.student_info_id = Student_Info.student_info_id 
INNER JOIN Trimester_Payment ON Payment_Transaction.trimester_id = Trimester_Payment.trimester_id 
INNER JOIN Transaction_Info ON Payment_Transaction.trans_info_id = Transaction_Info.trans_info_id 
GROUP BY ID_Number, trans_name;

edited Jul 07 '15 at 10:57

answered Jul 01 '15 at 10:16

Mackan

6,200
2
25
45

`Message SQL00000 An error occurred while the batch was being executed.` I've got this error when I try to run this one. – Jen143Me Jul 01 '15 at 10:21
It is more of an example to show you how group by works. I, or anyone else, can't tell you what to do with the remaining columns since it might invalidate your data. The summary is: if you want to group a set of columns you also have to choose what to do with the others (skip them or aggregate them). And I left out the `CREATE VIEW` bit since this was not part of the problem. Since I don't know your data types I guess `AVG(amount)` could fail if it's not a numeric type. – Mackan Jul 01 '15 at 10:32
Thank YOu again,,it really helps! – Jen143Me Jul 01 '15 at 10:41
I wonder why this view cannot be seen in my model. I have this error when I try to add the model : `dbo.Payment_Transaction_vw does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review our schema, add the correct keys, and uncomment it.` – Jen143Me Jul 07 '15 at 10:15
@Jen143Me That sounds like an Entity Framework error, or? http://stackoverflow.com/questions/24792259/error-6002-the-table-view-does-not-have-a-primary-key-defined – Mackan Jul 07 '15 at 10:46
But the view cannot be seen in the designer mode or in my model. The view can only be seen in the database. – Jen143Me Jul 07 '15 at 10:50
@Jen143Me I understand, but that is an EF issue. I found loads of info when googling part of your error message, and I'm sure you can too. If that doesn't help you can always ask a new question regarding EF and that error. You can try with adding an alias as **Id** for the primary key: `ISNULL(MAX(payment_trans_id),0) AS Id`, but I'm not sure that will be enough. I updated the answer with complete code. – Mackan Jul 07 '15 at 10:55

Group By not working in SQL Server

2 Answers2