amir-aa
دوشنبه 06 شهریور 1391, 09:18 صبح
select sarfactor.onvan,sarfactor.kind,sarfactor.sal,sarfa ctor.mah,sarfactor.rooz,sarfactor.moshtari,factor. idf,factor.name,factor.takhfif,factor.tedad,factor .tozih,factor.vahed,factor.vahed-factor.takhfif as 'قابل پرداخت',sum(factor.vahed-factor.takhfif) as 'مبلغ کل' from factor inner join sarfactor on sarfactor.id=factor.idf where idf=@code group by idf
ارورش
Column 'sarfactor.onvan' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
چکار کنم؟؟؟!!
majjjj
دوشنبه 06 شهریور 1391, 11:32 صبح
این کد رو یک امتحان بکنید لطفا
select min(sarfactor.onvan)
,min(sarfactor.kind)
,min(sarfactor.sal)
,min(sarfactor.mah)
,min(sarfactor.rooz)
,min(sarfactor.moshtari)
,factor.idf
,min(factor.name)
,min(factor.takhfif)
,min(factor.tedad)
,min(factor.tozih)
,min(factor.vahed)
,min(factor.vahed-factor.takhfif) as 'قابل پرداخت'
,sum(factor.vahed-factor.takhfif) as 'مبلغ کل'
from factor inner join sarfactor on sarfactor.id=factor.idf
where idf=@code
group by idf
majjjj
سه شنبه 07 شهریور 1391, 08:59 صبح
ببینید یک سیستم بهینه فاکتور زنیه
خب ممکنه توی یک فاکتور چند تامحصول وجود داشته باشه!!برای اینکار باید همه محصولات انتخاب بشن و تعدادشون در تفاضل قیمت واحد وتخفیف ضرب بشه...
اینجوری که شما گفتید فقط یک سطر انتخاب میشه
خوب اینجوری بهتر شد
اینکه یک فاکتور چند تا محصول مختلف داره و میخواهید نام محصولها را هم داشته باشید یعنی اینکه تو گروپ بای خودتون نام محصولها را هم اضافه کنید
اینکه تعدادشون در تفاضل واحد و تخفیف ضرب بشه یعنی اینکه باید از تابع count استفاده کنید
یعنی کدتون شبیه به این میشه
select min(sarfactor.onvan),min(sarfactor.kind),min(sarfa ctor.sal),min(sarfactor.mah),min(sarfactor.rooz),m in(sarfactor.moshtari),factor.idf,factor.name,SUM( factor.takhfif),count(factor.tedad),min(factor.toz ih),min(factor.vahed),min(factor.vahed-factor.takhfif) as 'قابل پرداخت',sum(factor.vahed-factor.takhfif) as 'مبلغ کل' from factor inner join sarfactor on sarfactor.id=factor.idf where idf=@code group by idf,name
البته این نکته رو فراموش نکنید که اگه محصولهاتون id دارن از id اونها بجای نام تو group by استفاده کنید.
majjjj
چهارشنبه 08 شهریور 1391, 08:02 صبح
من میخوام چیزی یاد بگیرم که باهاش بتونم یک تابع رو در میون چندتا مورد غیر تابع select کنم همین
خوب من هم همین کار رو کردم
لطفا یک نگاهی به این بکنید
Home Library Learn Downloads Support Community Sign in | United States - English | |
MSDN Library Servers and Enterprise Development SQL Server SQL Server 2012 Product Documentation Books Online for SQL Server 2012 Database Engine Transact-SQL Reference (Database Engine) Data Manipulation Language (DML) Statements (Transact-SQL) SELECT (Transact-SQL) SELECT Clause (Transact-SQL) SELECT Examples (Transact-SQL) FOR Clause (Transact-SQL) GROUP BY (Transact-SQL) HAVING (Transact-SQL) INTO Clause (Transact-SQL) ORDER BY Clause (Transact-SQL) OVER Clause (Transact-SQL)
Community Content
Add code samples and tips to enhance this topic.
More... GROUP BY (Transact-SQL)
Other Versions SQL Server 2008 R2SQL Server 2008SQL Server 2005
7 out of 11 rated this helpful - Rate this topic Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server 2012. One row is returned for each group. Aggregate functions in the SELECT clause <select> list provide information about each group instead of individual rows.
The GROUP BY clause has an ISO-compliant syntax and a non-ISO-compliant syntax. Only one syntax style can be used in a single SELECT statement. Use the ISO compliant syntax for all new work. The non-ISO compliant syntax is provided for backward compatibility.
In this topic, a GROUP BY clause can be described as general or simple:
A general GROUP BY clause includes GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP.
A simple GROUP BY clause does not include GROUPING SETS, CUBE, ROLLUP, WITH CUBE, or WITH ROLLUP. GROUP BY (), grand total, is considered a simple GROUP BY.
Transact-SQL Syntax Conventions (Transact-SQL)
Syntax
--------------------------------------------------------------------------------
Copy
ISO-Compliant Syntax
GROUP BY <group by spec>
<group by spec> ::=
<group by item> [ ,...n ]
<group by item> ::=
<simple group by item>
| <rollup spec>
| <cube spec>
| <grouping sets spec>
| <grand total>
<simple group by item> ::=
<column_expression>
<rollup spec> ::=
ROLLUP ( <composite element list> )
<cube spec> ::=
CUBE ( <composite element list> )
<composite element list> ::=
<composite element> [ ,...n ]
<composite element> ::=
<simple group by item>
| ( <simple group by item list> )
<simple group by item list> ::=
<simple group by item> [ ,...n ]
<grouping sets spec> ::=
GROUPING SETS ( <grouping set list> )
<grouping set list> ::=
<grouping set> [ ,...n ]
<grouping set> ::=
<grand total>
| <grouping set item>
| ( <grouping set item list> )
<empty group> ::=
( )
<grouping set item> ::=
<simple group by item>
| <rollup spec>
| <cube spec>
<grouping set item list> ::=
<grouping set item> [ ,...n ]
Copy
Non-ISO-Compliant Syntax
[ GROUP BY [ ALL ] group_by_expression [ ,...n ]
[ WITH { CUBE | ROLLUP } ]
]
Arguments
--------------------------------------------------------------------------------
<column_expression>
Is the expression on which the grouping operation is performed.
ROLLUP ( )
Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.
The number of groupings that is returned equals the number of expressions in the <composite element list> plus one. For example, consider the following statement.
Copy
SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c);
One row with a subtotal is generated for each unique combination of values of (a, b, c), (a, b), and (a). A grand total row is also calculated.
Columns are rolled up from right to left. The column order affects the output groupings of ROLLUP and can affect the number of rows in the result set.
CUBE ( )
Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
CUBE outputs a grouping for all permutations of expressions in the <composite element list>.
The number of groupings that is generated equals (2n), where n = the number of expressions in the <composite element list>. For example, consider the following statement.
Copy
SELECT a, b, c, SUM (<expression>)
FROM T
GROUP BY CUBE (a,b,c);
One row is produced for each unique combination of values of (a, b, c), (a, b), (a, c), (b, c), (a), (b) and (c) with a subtotal for each row and a grand total row.
Column order does not affect the output of CUBE.
GROUPING SETS ( )
Specifies multiple groupings of data in one query. Only the specified groups are aggregated instead of the full set of aggregations that are generated by CUBE or ROLLUP. The results are the equivalent of UNION ALL of the specified groups. GROUPING SETS can contain a single element or a list of elements. GROUPING SETS can specify groupings equivalent to those returned by ROLLUP or CUBE. The <grouping set item list> can contain ROLLUP or CUBE.
( )
The empty group generates a total.
Non-ISO Compliant Syntax
ALL
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Includes all groups and result sets, even those that do not have any rows that meet the search condition specified in the WHERE clause. When ALL is specified, null values are returned for the summary columns of groups that do not meet the search condition. You cannot specify ALL with the CUBE or ROLLUP operators.
GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query. GROUP BY ALL will fail on columns that have the FILESTREAM attribute.
group_by_expression
Is an expression on which grouping is performed. group_by_expression is also known as a grouping column. group_by expression can be a column or a non-aggregate expression that references a column returned by the FROM clause. A column alias that is defined in the SELECT list cannot be used to specify a grouping column.
Note
Columns of type text, ntext, and image cannot be used in group_by_expression.
For GROUP BY clauses that do not contain CUBE or ROLLUP, the number of group_by_expression items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate worktable that is needed to hold intermediate query results. A maximum of 12 grouping expressions is permitted when CUBE or ROLLUP is specified.
xml data type methods cannot be specified directly in group_by_expression. Instead, refer to a user-defined function that uses xml data type methods inside it, or refer to a computed column that uses them.
WITH CUBE
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.
The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified.
WITH ROLLUP
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.
Important
Distinct aggregates, for example, AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name), are not supported when you use CUBE or ROLLUP. If these are used, the SQL Server Database Engine returns an error message and cancels the query.
Remarks
--------------------------------------------------------------------------------
Expressions in the GROUP BY clause can contain columns of the tables, derived tables or views in the FROM clause. The columns are not required to appear in the SELECT clause <select> list.
Each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:
The following statements are allowed:
Copy
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;
The following statements are not allowed:
Copy
SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;
If aggregate functions are included in the SELECT clause <select list>, GROUP BY calculates a summary value for each group. These are known as vector aggregates.
Rows that do not meet the conditions in the WHERE clause are removed before any grouping operation is performed.
The HAVING clause is used with the GROUP BY clause to filter groups in the result set.
The GROUP BY clause does not order the result set. Use the ORDER BY clause to order the result set.
If a grouping column contains null values, all null values are considered equal, and they are put into a single group.
You cannot use GROUP BY with an alias to replace a column name in the AS clause unless the alias replaces a column name in a derived table in the FROM clause.
Duplicate grouping sets in a GROUPING SETS list are not eliminated. Duplicate grouping sets can be generated by specifying a column expression more than one time or by listing a column expression also generated by a CUBE or ROLLUP in the GROUPING SETS list.
Distinct aggregates, for example, AVG (DISTINCT column_name), COUNT (DISTINCT column_name), and SUM (DISTINCT column_name) are supported with ROLLUP, CUBE, and GROUPING SETS.
ROLLUP, CUBE, and GROUPING SETS cannot be specified in an indexed view.
GROUP BY or HAVING cannot be used directly on columns of ntext, text, or image. These columns can be used as arguments in functions that return a value of another data type, such as SUBSTRING() and CAST().
xml data type methods cannot be specified directly in a <column_expression>. Instead, refer to a user-defined function that uses xml data type methods inside it, or refer to a computed column that uses them.
GROUP BY Limitations for GROUPING SETS, ROLLUP, and CUBE
Syntax Limitations
GROUPING SETS are not allowed in the GROUP BY clause unless they are part of a GROUPING SETS list. For example, GROUP BY C1, (C2,..., Cn) is not allowed but GROUP BY GROUPING SETS (C1, (C2, ..., Cn)) is allowed.
GROUPING SETS are not allowed inside GROUPING SETS. For example, GROUP BY GROUPING SETS (C1, GROUPING SETS (C2, C3)) is not allowed.
The non-ISO ALL, WITH CUBE, and WITH ROLLUP keywords are not allowed in a GROUP BY clause with the ROLLUP, CUBE or GROUPING SETS keywords.
Size Limitations
For simple GROUP BY, there is no limit on the number of expressions.
For a GROUP BY clause that uses ROLLUP, CUBE, or GROUPING SETS, the maximum number of expressions is 32, and the maximum number of grouping sets that can be generated is 4096 (212). The following examples fail because the GROUP BY clause is too complex:
The following examples generate 8192 (213) grouping sets.
Copy
GROUP BY CUBE (a1, ..., a13)
GROUP BY a1, ..., a13 WITH CUBE
The following example generates 4097 (212 + 1) grouping sets.
Copy
GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )
The following example also generates 4097 (212 + 1) grouping sets. Both CUBE () and the () grouping set produce a grand total row and duplicate grouping sets are not eliminated.
Copy
GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())
Support for ISO and ANSI SQL-2006 GROUP BY Features
In SQL Server 2012, the GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. Error 144 is returned.
SQL Server 2012 supports all GROUP BY features that are included in the SQL-2006 standard with the following syntax exceptions:
Grouping sets are not allowed in the GROUP BY clause unless they are part of an explicit GROUPING SETS list. For example, GROUP BY Column1, (Column2, ...ColumnN) is allowed in the standard but not in SQL Server. GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) or GROUP BY Column1, Column2, ... ColumnN is allowed. These are semantically equivalent to the previous GROUP BY example. This is to avoid the possibility that GROUP BY Column1, (Column2, ...ColumnN) might be misinterpreted as GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)). This is not semantically equivalent.
Grouping sets are not allowed inside grouping sets. For example, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS (C1, C2, ...Cn)) is allowed in the SQL-2006 standard but not in SQL Server. SQL Server 2012 allows GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) or GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). These examples are semantically equivalent to the first GROUP BY example and have a clearer syntax.
GROUP BY [ALL/DISTINCT] is not allowed in a general GROUP BY clause or with the GROUPING SETS, ROLLUP, CUBE, WITH CUBE or WITH ROLLUP constructs. ALL is the default and is implicit.
Comparison of Supported GROUP BY Features
The following table describes the GROUP BY features that are supported based upon the version of SQL Server and the database compatibility level.
Feature
SQL Server 2005 Integration Services
SQL Server compatibility level 100 or higher
SQL Server 2008 or later with compatibility level 90
DISTINCT aggregates
Not supported for WITH CUBE or WITH ROLLUP.
Supported for WITH CUBE, WITH ROLLUP, GROUPING SETS, CUBE, or ROLLUP.
Same as compatibility level 100.
User-defined function with CUBE or ROLLUP name in the GROUP BY clause
User-defined function dbo.cube (arg1,...argN) or dbo.rollup (arg1,...argN) in the GROUP BY clause is allowed.
For example:
Copy
SELECT SUM (x)
FROM T
GROUP BY dbo.cube(y);
User-defined function dbo.cube (arg1,...argN) or dbo.rollup (arg1,...argN) in the GROUP BY clause is not allowed.
For example:
Copy
SELECT SUM (x)
FROM T
GROUP BY dbo.cube(y);
The following error message is returned: "Incorrect syntax near the keyword 'cube'|'rollup'."
To avoid this problem, replace dbo.cube with [dbo].[cube] or dbo.rollup with [dbo].[rollup].
The following example is allowed:
Copy
SELECT SUM (x)
FROM T
GROUP BY [dbo].[cube](y);
User-defined function dbo.cube (arg1,...argN) or dbo.rollup (arg1,...argN) in the GROUP BY clause is allowed
For example:
Copy
SELECT SUM (x)
FROM T
GROUP BY dbo.cube(y);
GROUPING SETS
Not supported
Supported
Supported
CUBE
Not supported
Supported
Not supported
ROLLUP
Not supported
Supported
Not supported
Grand total, such as GROUP BY ()
Not supported
Supported
Supported
GROUPING_ID function
Not supported
Supported
Supported
GROUPING function
Supported
Supported
Supported
WITH CUBE
Supported
Supported
Supported
WITH ROLLUP
Supported
Supported
Supported
WITH CUBE or WITH ROLLUP "duplicate" grouping removal
Supported
Supported
Supported
Examples
--------------------------------------------------------------------------------
A. Using a simple GROUP BY clause
The following example retrieves the total for each SalesOrderID from the SalesOrderDetail table.
Copy
USE AdventureWorks2012;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
B. Using a GROUP BY clause with multiple tables
The following example retrieves the number of employees for each City from the Address table joined to the EmployeeAddress table.
Copy
USE AdventureWorks2012;
GO
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress AS bea
INNER JOIN Person.Address AS a
ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;
C. Using a GROUP BY clause with an expression
The following example retrieves the total sales for each year by using the DATEPART function. The same expression must be present in both the SELECT list and GROUP BY clause.
Copy
USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
D. Using a GROUP BY clause with a HAVING clause
The following example uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set.
Copy
USE AdventureWorks2012;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
HAVING DATEPART(yyyy,OrderDate) >= N'2003'
ORDER BY DATEPART(yyyy,OrderDate);
See Also
--------------------------------------------------------------------------------
Reference
GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
SELECT Clause (Transact-SQL)
vBulletin® v4.2.5, Copyright ©2000-1404, Jelsoft Enterprises Ltd.