How to use group by clouse in joins

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

SELECT        tb_Ticket.TicketName, tb_Sales.DateDraw, tb_UserManagement.UserName, 
tb_Sales.BillNo, tb_PrizeNumbers.PrizePosition, tb_PrizeNumbers.PrizeNo, 
                         tb_PrizeDetails.Stockist, tb_PrizeDetails.Substockist, tb_PrizeDetails.Agent
FROM            tb_Bill INNER JOIN
                         tb_PrizeDetails INNER JOIN
                         tb_LotteryPrizeDetails ON tb_PrizeDetails.TID = tb_LotteryPrizeDetails.TID INNER JOIN
                         tb_PrizeNumbers ON tb_LotteryPrizeDetails.TID = tb_PrizeNumbers.TID ON 
                         tb_Bill.SerialNo = tb_PrizeNumbers.PrizeNo CROSS JOIN
                         tb_Ticket INNER JOIN
                         tb_Sales ON tb_Ticket.TicketID = tb_Sales.TicketID INNER JOIN
                         tb_UserManagement ON tb_Sales.UserID = tb_UserManagement.UserID INNER JOIN
                         tb_WinningTable ON tb_Ticket.TicketID = tb_WinningTable.TicketID GROUP BY tb_Bill.BillNo

Column 'tb_Ticket.TicketName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

this is the error showing.. help me plzzzzzzzzzz

What I have tried:

SQL

SELECT        tb_Ticket.TicketName, tb_Sales.DateDraw, tb_UserManagement.UserName, 
tb_Sales.BillNo, tb_PrizeNumbers.PrizePosition, tb_PrizeNumbers.PrizeNo, 
                         tb_PrizeDetails.Stockist, tb_PrizeDetails.Substockist, tb_PrizeDetails.Agent
FROM            tb_Bill INNER JOIN
                         tb_PrizeDetails INNER JOIN
                         tb_LotteryPrizeDetails ON tb_PrizeDetails.TID = tb_LotteryPrizeDetails.TID INNER JOIN
                         tb_PrizeNumbers ON tb_LotteryPrizeDetails.TID = tb_PrizeNumbers.TID ON 
                         tb_Bill.SerialNo = tb_PrizeNumbers.PrizeNo CROSS JOIN
                         tb_Ticket INNER JOIN
                         tb_Sales ON tb_Ticket.TicketID = tb_Sales.TicketID INNER JOIN
                         tb_UserManagement ON tb_Sales.UserID = tb_UserManagement.UserID INNER JOIN
                         tb_WinningTable ON tb_Ticket.TicketID = tb_WinningTable.TicketID GROUP BY tb_Bill.BillNo

Column 'tb_Ticket.TicketName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

this is the error showing.. help me plzzzzzzzzzz

Posted 28-Mar-16 21:41pm

Member 12386982

Updated 28-Mar-16 22:10pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-03-28T22:10:00

A GROUP BY clause "collapses" a range of rows into a single row with identical values - and you can only return those identical values directly - all other columns from the group of rows can only be returned as an aggregate value - the SUM, the AVERAGE, and so on.
So if you have three rows

ID   Date        Sales
1    2016-01-02  100
2    2016-01-02  200
3    2016-01-02  300
4    2016-01-03  400
5    2016-01-03  500
6    2016-01-03  600

And you GROUP BY Date, then you can't return the Sales value directly because SQL doesn't know which row value to return:

SQL

SELECT Date, Sales FROM MyTable GROUP BY Date

Date        Sales
2016-01-02  ??? Should this be 100, 200, or 300?
2016-01-03  ??? Should this be 400, 500, or 600?

You can return an aggregate value though:

SQL

SELECT Date, SUM(Sales) FROM MyTable GROUP BY Date

Date        Sales
2016-01-02  600
2016-01-03  1500

You are trying to return values which aren't necessarily a single row value because you GROUP BY BillNo so SQL complains.
You need to work out exactly what your data looks like, and what you need to return - and we can;t help you with that, because we have no idea what your data is, much less what you want to select!