Does The Group By Have To Include All Fields From The SELECT Clause?

Dec 3, 2007

hey all,

say i have the following function

SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR

FROM GLF_CHART_ACCT

INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1)
AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)

GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
GLF_CHART_ACCT.SELN_TYPE1_CODE

HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;

I get errors if not all the fields are included in the group by clause.

what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??

Cheers,

Justin

View 5 Replies


ADVERTISEMENT

SQL 2012 :: Include Columns In Index That Are In Where Clause / Select List And Join

Jun 2, 2014

Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.

View 4 Replies View Related

Non Aggregated Fields In Group By Clause

Jul 8, 2013

I'd like to have all distinct recordIDs with relevant text associated with them. Each record has 3 text boxes in different languages. Each text in different language is defined by an AttributeDefinitionID. This is my query:

Select a.entryID, g.GroupName, c.CategoryName as ExperienceType,
e.AttributeValue as EnglishWording,
e1.AttributeValue as GermanWording,
e2.AttributeValue as RussianWording,
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid

[Code] ....

but in the results I get additional rows for each record even if the record doesnt have all three text boxes populated and there is only EnglishText for example.

EntryID GrouPName EnglishWording GermanWording RussianWording
1586 Red abc NULL NULL
1586 Red NULL NULL NULL
3566 Yellow NULL Hallo Welt NULL
3566 Yellow NULL NULL NULL
3566 Yellow Hello world NULL NULL
3566 Yellow Hello world Hallo Welt NULL

1586 should only return the first line with English wording.
3566 should return the last line that shows both English and German wording populated

View 19 Replies View Related

Many Fields Update From A Group By Clause

Jul 20, 2005

Hi All,In Oracle, I can easily make this query :UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'I cannot seem to be able to do the same thing with MS-SQL. There areonly 2 ways I've figured out, and I fear performance cost in both cases,which are these :1)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'and then the same statement but with f2, and2)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6),f2=(SELECT SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'Is there a way with MS-SQL to do the Oracle equivalent in this case ?Thanks,Michel

View 3 Replies View Related

How To Select Last Rows In Group By Clause

Nov 7, 2008

I have a table which stores datewise Transactions of different items. Fields and sample data is

RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

View 6 Replies View Related

Select Names / Number - Clause Group By

Aug 30, 2013

I try this sql query:

Code:
SELECT
[NAMES], [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
[NAMES], [NUMBER];

The output is:

Code:
NAMESNUMBER
BENCORE S.R.L.44644
BENCORES.R.L. 44644

I need instead this other output:

Code:
NAMESNUMBER
BENCORE S.R.L.44644

View 2 Replies View Related

'Include Clause' In Create Index Syntax

Mar 26, 2008

What is the equivalent for INCLUDE clause (in Create index syntax) in SQL Server 2000.
SQL Server 2005 will support Include clause in Create index syntax . How to attain it in SQL Server 2000

Example : I have below query executable in SQL Server 2005

CREATE INDEX Index_Name ON mytable(col1 ASC) INCLUDE (name,id);

What is the equivalent for the above query in SQL server 2000

Thanks ,
Sushma

View 1 Replies View Related

How To Include Variable In CURSOR SQL Filter Clause?

Jul 23, 2005

After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim

View 1 Replies View Related

Include ID Field In GROUP BY Statement

May 15, 2007

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
 The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName      CostApple             0.63Pear              0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID      FruitName      Cost2       Apple             0.634       Pear              0.89
 Thanks.

View 9 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Sql Select To Include Date?

Apr 19, 2008

I have a table of parts and want to display some statistics from it.  The parts table has several fields in it, but the two that I need to use in my sql select queries are "PartUserID" and "DateUseBy", but I don't know how to use the "dateuseby" in the second example.Can someone help me out with #2?1. Display the total number of parts owned by a user:SQL Query:        SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId)"        OnSelecting="sqlPartCount_Selecting">        <SelectParameters>            <asp:Parameter Name="PartUserID" />        </SelectParameters>Code-behind:    protected void sqlPartCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {        e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey;    }    2. Display the total number of parts owned by a user that are to be used in the current year. The table has a field "dateuseby" that should be used for the "Where" but I don't know how to get it.SQL Query:How to I include the "usebydate" so that it will use 2008 as the year ? I wrote the following query as an example of what I'm trying to get.        SelectCommand="SELECT COUNT (*) FROM [ZCPart] WHERE (ZCPart.PartUserId = @PartUserId, ZCPart.DateUseById = @PartUseById)"         onselecting="sqlPartYearCount_Selecting">        <SelectParameters>            <asp:Parameter Name="PartUserID" />            <asp:Parameter Name="DateUseByID" />        </SelectParameters>Code-behind:    protected void sqlPartYearCount_Selecting(object sender, SqlDataSourceSelectingEventArgs e)    {        e.Command.Parameters["@PartUserID"].Value = Membership.GetUser().ProviderUserKey;                // ? How do I change the date in the field to be in "2008" format and then put that in the sql query?        //e.Command.Parameters["@DateUseByID"].Value = currentYear;        //currentYear currentDateTime = DateTime.Now;    }

View 7 Replies View Related

Include / In Select Statement

Mar 21, 2006

I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000

View 2 Replies View Related

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View 5 Replies View Related

T-SQL (SS2K8) :: Include Row Values As Columns In Select Query

Apr 28, 2015

How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.

Existing table layout

table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go

Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go

Once I figure out how to get the data in the layout above, I will need to join the table with my record table.

table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress

I would like to be able to join the tables in the query below for the final output.

Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID

How I can get the data in the desired #tempComment table layout mentioned above?

View 2 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View 10 Replies View Related

Need Help With Group Clause.

Mar 23, 2008

 HiI have this query I made but I am not sure how to fully use the group by clause.  IF @option = 'day'
BEGIN
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded,
CONVERT(VARCHAR(10),TimeDateStamp,101) As TimeDateStamp
FROM Charts
WHERE TimeDateStamp
BETWEEN DATEADD(d,-7,DATEDIFF(d,0,getdate() + 1))
AND
DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,getdate())+ 1 , 0)) AND UserID = @UserID
GROUP BY UserID,TimeDateStamp
ENDSo I get a result like this:BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0025103/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0020003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0014003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008 but I really just want it to be thisBB7EFE81-B532-46DC-B8D1-4E7A4186EEB001071 03/23/2008See I want everything with the same user name and date to be grouped together. But when I do that in my group clause it separates them all.  Yet if I don't have my timeDateStamp in the group clause I get a syntax error.Thanks   

View 4 Replies View Related

Cannot Use GROUP BY Clause The Way I Want To?

Jun 28, 2004

I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?

View 3 Replies View Related

Group By Clause

Nov 2, 2007

in select apart of my statment I have the below code, but I want to exclude it from the group by clause, is there a way I can do this??


(cast(timesheethours.hoursworked as char) + '' + cast

(timesheethours.payrate as char) + ''+ objects.FileAs) as 'workers details',


Thanks

Dave,

View 4 Replies View Related

Group By Clause

May 10, 2008

Hi all,

Got a problem here, I am not quite familiar yet with the sql group by clause. As an illustration, I have a table with column StoreName and Sales.

StoreName Sales
DFA_Main 50
DFA_Main 50
DFA_Branch 60
DFA_Branch 60
DFA_OtherBranch 10
MMDA_Main 50
MMDA_Main 50
MMDA_Branch 30
MMDA_Branch 30

In my understanding if we are going to group this table by StoreName the result will be:

StoreName Sales
DFA_Main 100
DFA_Branch 120
DFA_OtherBranch 10
MMDA_Main 100
MMDA_Branch 60

But what would be the sql statement to produce an output like this:

StoreName Sales
DFA 230
MMDA 160

Is it possible for me to do that? Thanks for the help in advance.

===============
JSC0624
===============

View 1 Replies View Related

HAVING Clause Cannot Be Used Without GROUP

Oct 17, 2013

In SQL, HAVING clause cannot be used without GROUP BY operation.

View 9 Replies View Related

How To Use The Group Clause

Dec 20, 2005

Hi all,

I have this stored procedure and I want it to produce the results to GROUP BY Style1, Style2, StyleColor, Whrse_No, Bin_No ...... This procedure is used by a crystal report file to produce a report.... Below is the code:
( I think that the problem is in that some fields which are not summed have to be somehow gropued or modified
)

------------------ Seelct part simply slects several variables where sum of them are summed

select Style1 ,style2,StyleColor,Description,Whrse_No, Whrse_Desc, Bin_no, size_cd, Nbr_Sizes

, Size_Desc01, Size_Desc02, Size_Desc03, Size_Desc04, Size_Desc05
, Size_Desc06, Size_Desc07,Size_Desc08, Size_Desc09, Size_Desc10
, Size_Desc11, Size_Desc12, Size_Desc13, Size_Desc14, Size_Desc15 , OnHandQty_Total = sum ( OnHandQty_Total)
, OnHandQty_Sz1 = sum (OnHandQty_Sz1), OnHandQty_Sz2 = sum (OnHandQty_Sz2), OnHandQty_Sz3 = sum (OnHandQty_Sz3), OnHandQty_Sz4 = sum (OnHandQty_Sz4), OnHandQty_Sz5 = sum (OnHandQty_Sz5)
, OnHandQty_Sz6 = sum (OnHandQty_Sz6), OnHandQty_Sz7 = sum (OnHandQty_Sz7), OnHandQty_Sz8 = sum (OnHandQty_Sz8), OnHandQty_Sz9 = sum (OnHandQty_Sz9), OnHandQty_Sz10 = sum (OnHandQty_Sz10)
, OnHandQty_Sz11 = sum (OnHandQty_Sz11), OnHandQty_Sz12 = sum (OnHandQty_Sz12), OnHandQty_Sz13 = sum (OnHandQty_Sz13), OnHandQty_Sz14 = sum (OnHandQty_Sz14), OnHandQty_Sz15 = sum (OnHandQty_Sz15)
, Total_OnHandNeg
, CompanyName
, Date


from #rInventoryOnHandStyle_Whse

Where 1 = 1

' + @sAND10 + '

------------- Group Rule

Group By Style1, style2, StyleColor, Whrse_No, Bin_no

' + @OrderBy + '


'
--)

EXEC(@cmd)
GO

View 2 Replies View Related

SQL--- GROUP BY Clause

Mar 21, 2006

Hi,I was wondering if anyone out there can help me with this SQL problem:I have a database that has two tables: EMPLOYEE and JOB_TITLEThe EMPLOYEE Table consists of a salary and job_title _code columns,among many others; the JOB_TITLE table contains job_title_code column,among many others.The SQL problem is: Select the employees' last names and Group them bySalary within their job_title_code. I am new to SQL statements andkinda puzzled on how to solve this problem. I would appreciate any helpI can get on this. Thanks a lot in advance.

View 1 Replies View Related

Need Help Using GROUP BY Clause

Sep 24, 2006

I have two tables
A. TEST_SUBJECTS_TBL with the following columns . This table contains the subjects in a test
1. TEST_SUBJECT_ID PK
2. SUBJECT_ID FK
3. TEST_ID FK
4. PM // This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns This table stores the marks scored by students for each subject
1. TEST_SUBJECT_ID FK
2. STUDENT_ID FK
3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID MaxMarks MinMarks AvgMarks TotalStudentsPassed
___________________________________________________________________
1 90 30 44 11 6
2 80 24 22 33 8
......
......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.

View 7 Replies View Related

GROUP BY CLAUSE

Sep 24, 2007

Greetings!



Why does the select statement must have the same non aggregated columns which are also part of the group by clause?



I have a feeling it's to do with the way the SQL Server database engine actually executes the query? i.e. the select part is the last thing the engine performs (after doing the joins and the filtering etc...).



Your help would be appreciated.

View 2 Replies View Related

Use Of Group By Clause

Oct 10, 2007


create table A
(
USERID int,
USER varchar(20),
TTYPE varchar(20),
DETAIL varchar(20),
);


insert into A(
1,'X','Credit','Amount xxx',
1,'X','Debit', 'Amount xxx',
2,'Y','Debit', 'Amount xx',
2,'Y','Debit', 'Amount xxx',
1,'X','Debit', 'Amount xxxx',
1,'X','Credit', 'Amount xxxx',

);

Create table B
(
USERID int,
TRANSACTION int
);


insert into B select USERID, COUNT(case when TTYPE='Credit' 1 else null end) as TRANSACTION from A group by USERID


[Error Code: 8118, SQL State: S1000] Column 'A.DETAIL' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any suggestions......

View 2 Replies View Related

Need Help Using GROUP BY Clause

Sep 24, 2006

I have two tables
A. TEST_SUBJECTS_TBL with the following columns . This table contains the subjects in a test
1. TEST_SUBJECT_ID PK
2. SUBJECT_ID FK
3. TEST_ID FK
4. PM This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns This table stores the marks scored by students for each subject
1. TEST_SUBJECT_ID FK
2. STUDENT_ID FK
3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID MaxMarks MinMarks AvgMarks TotalStudentsPassed
___________________________________________________________________
1 90 30 44 11 6
2 80 24 22 33 8
......
......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem. How do i find the total students passed ?

View 2 Replies View Related

Duplicates And The GROUP BY Clause

Nov 12, 2003

I am making a website where users go to a page that lists every Program in their area. The first time the page loads they see all the Programs, then then can filter it down with drop down lists. I have everything working except for the Category because some programs have more than one category. The select is working good but I get duplicates.

Here it is:

SELECT DISTINCT
p.ProgramID,
p.ProgramName,
p.ProgramCity,
p.ProgramState,
p.ProgramCountyID,
p.ProgramHours,
p.ProgramContactName,
p.ProgramPhone,
p.ProgramEmail,
p.ProgramGrades,
p.ProgramTransportation,
pc.ProgramID,
pc.CategoryID

FROM
Programs p,
ProgramCategories pc

WHERE
p.ProgramCountyID IS NOT NULL AND
p.ProgramCity IS NOT NULL AND
p.ProgramHours IS NOT NULL AND
p.ProgramGrades IS NOT NULL AND
p.ProgramTransportation IS NOT NULL AND
p.ProgramID = pc.ProgramID AND
pc.CategoryID IS NOT NULL


GROUP BY
p.ProgramID

ORDER BY
p.ProgramName ASC



When I have just p.ProgramID in the GROUP BY clause, I get the error:

"column name" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But when I put all the column names in the GROUP BY clause, I still get duplicates. What can I do to stop this. When the user selects a category the pc.CategoryID IS NOT NULL changes to pc.CategoryID = 3 (or whatever they select) and everything works the way its supposed to. I just want each individual program to show only once when the page first loads.

Thanks for your time
Dave

View 2 Replies View Related

GROUP BY/ HAVING CLAUSE Problem

Sep 27, 2005

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT     SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreignFROM         Orders INNER JOIN                      Processing ON Orders.ID = Processing.Order_IDWHERE     (Processing.Orderdate = '20050915') AND (Processing.status = 1)GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL ENDHAVING      (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.???.netsports

View 5 Replies View Related

Group By Clause Limitation

Oct 14, 2006

Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode

Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?

View 2 Replies View Related

Using Group By And Order By Clause

May 21, 2014

I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.

View 3 Replies View Related

Best Practices: GROUP BY Clause

May 20, 2004

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

Thanks

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved