Using Group By Clause In Union Query

Sep 19, 2007

Hi all,

I have two table having datas like

Table1
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 3 2
x 0 1 0 2
x 0 0 2 1
y 1 5 2 0


Table2
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 0 1 4
y 1 0 3 1
y 1 2 0 0
y 0 0 5 1

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t

Result:
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 5 5
y 1 5 2 0
x 0 0 1 4
y 2 2 8 2

But i need the result like i.e grouped by column 'A'
--------------------------------------------------------------------
A C1 C2 C3 C4
--------------------------------------------------------------------
x 0 1 6 9
y 3 7 10 2

select * from(
select A,C1,C2,C3,C4 from Table1 group by A
union
select A,C1,C2,C3,C4 from Table2 group by A
)as t group by A

The above query gives the following error
[Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help me out.

-Anand

View 3 Replies


ADVERTISEMENT

Group By Clause Query Help

Jul 23, 2005

This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ

View 2 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

SQL Query Problem In Group By Clause

Jan 26, 2004

I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,

What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus



Regards.


MATEEN

View 6 Replies View Related

GROUP By Clause, /sub Query Problems

Aug 15, 2005

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)???.NetSports

View 1 Replies View Related

Query Based On Group By Clause

Jun 21, 2008

Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View 2 Replies View Related

Help With Simple Query Using Group By And Where Clause

Jun 13, 2006

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005



View 1 Replies View Related

Transact SQL :: Getting Last Register From A Query That Use A Group By Clause

Aug 13, 2015

I've got this set of registers (just an example) after ordering by the first 3 columns:

value_A value_B value_C ID date
1 2 3 YVIR 29/08/2015
1 2 3 ANTE 27/04/2015
1 2 3 REGO 20/02/2015

I need to get as a final result:

value_A value_B value_C ID date
1 2 3 REGO 29/08/2015

In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.

I've been trying to do this with the group by clause:

select
value_A, value_B, value_C, min(ID), max(date) -- or max(ID)
from table
group by value_A, value_B, value_C

But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.

In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.

This table is update by some one else, I just can access the information and use it as a source.

View 3 Replies View Related

Analysis :: Where Clause On Measure Group In MDX Query

Nov 11, 2015

When I am executing below MDX query, it's giving correct result with out any issue 
                     
SELECT 
              NON EMPTY 
              {
                     [Measures].[Daystorecieve] ,
                     [Measures].[PO Recieved],
            [Measures].[Post Award Milestone PO Analysis Count],
                     [Measures].[Powith80pct Received]

[Code] ....

After the successful execution of the above query, I am trying to filter on my measure group [Measures].

[Daystorecieve]  values not equal to "0". With minimum number of the dimension selection my query executing fine.

Please find the below query.                                   

SELECT 
              NON EMPTY 
              {
                     [Measures].[Daystorecieve] ,
                     [Measures].[PO Recieved],
            [Measures].[Post Award Milestone PO Analysis Count],
                     [Measures].[Powith80pct Received]
              }  ON COLUMNS,

[Code] ....

But, when I am trying to execute with total number of dimensions. It's running long time and giving out of memory exception. Is there any way to apply where condition on my measure group like where [Measures].[Daystorecieve]<>0.

View 6 Replies View Related

Union Clause

May 15, 2004

sql = "Select c1 from t1
sql = sql & " Union Select c1 from t2 Order By c1"

t1=c1 = 1,2,3
t2=c1 = 200,5,300

datatype=nvarchar

result is not ordered as 1,2,3,4,200,300 why

View 1 Replies View Related

UNION With WHERE Clause?

Jun 19, 2012

I have 2 tables(Customer,Staff) with the same column name 'email'. I would like to join both tables together where email="xxx@xx.com" to check if email exist already in the database. I have tried

Code:
Select email from (select email from staff union select email from customer) as 'CombinedEmail' where email = 'xxx@xx.com'

But it does not work at all. Is there anything wrong with my codes?

View 4 Replies View Related

Top Clause , Union And Order By

Dec 7, 2007

Hi,

I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.

The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):

QUERY 1

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411

order by s.ldate desc


RESULTS 1

DATE MDT IDPU Odometer DO Odometer Total Miles
12/6/2007 2041112810.6 12874.5 63.9

QUERY 2 (with Union)

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411

Union

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642

order by s.ldate desc

RESULTS 2

DATE MDT ID PU OdometerDO Odometer Total Miles
4/10/2007 20411 1207.21252.5 45.3
1/2/2007 2642 193652.6193817 164.4

As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?

Thanks!
Craig

View 5 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

Need To Union And Group Split Databases

Jun 10, 2008

We had a divestiture within our company. Now what used to be contained in one database in now split into two databases. One showing all history and one being all current data as of 6/1/2008. Is there an easy way to Union or Join these? Right now I'm currently doing a simple UNION ALL, but can't group the two select statements:

SELECT
Year,Location,QtySold
FROM
historydb

UNION ALL

SELECT
Year,Location,QtySold
FROM
currentdb

Can't do a subset and group both of these selects. How would some of you pro's do this? Right now I can put this in a simple view and then create a SP off of this view that would do this grouping, but it seems like I should be able to do it all in one query. Thanks.

View 2 Replies View Related

How To Group/list Top 3 Of Each Category W/o Using Union?

Jul 20, 2005

Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Reporting Services :: Using GROUP BY With UNION ALL

May 15, 2015

Here is my sql code. I'm using a "union all" to merge Incidents and Service Requests into one table.  This works fine when I don't use the "group by".  When using "group by" to get the total number of tickets per "Area" it is giving me duplicates.  So it is returning a distinct list of "Area" from both select statements. 

SELECT
IRAreaDN.DisplayNameas 'Area'
,Count(IR.Id) as 'Total Requests'
--,IRSupportGroupDN.DisplayNameas 'Support Group'
--, CAST(DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) AS DATE)as 'Created Date'
--, CreatedByUser.UserName as 'Created By User ID'

[Code] ....

View 2 Replies View Related

UNION With GROUP BY 'Invalid Column Name'

Mar 9, 2008


I have a query similar to the following, but it gives the following error:
'Invalid column name 'GroupName'.'


SELECT 'Primary' as GroupName, City

FROM PrimaryTable

UNION ALL

SELECT SpecialGroupName AS GroupName, City

FROM Table2

GROUP BY GroupName, City


GroupName is varchar(30)

Any ideas why this would fail. Thanks in advance.

Terry


View 6 Replies View Related

Group / Union Statement - Pull Unique Records From A Large Table

Sep 22, 2014

I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.

What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).

I have tried 2 statements:

#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;

#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));

Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.

Example:

NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)

I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?

View 1 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







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