SQL grouping statistics-Summary Product

Source: Internet
Author: User
Table tableone

Value Catalog
1 Cataloga
2 Catalogb
3 Catalogb
3 Cataloga
4 Cataloga
6 Catalogb


Requirement: Summarize the values according to the catalog.Product

Only scalar values can be used for input parameters in user-defined functions. Table cannot be used as a parameter input!

The input parameters of the stored procedure are different:

Data type. All data types (includingText,NtextAndImage. However,CursorThe data type can only be used for output parameters. If the specified data type isCursorThe varying and output keywords must also be specified.

Because a user-defined function can return a table.

  • Functions can be used in functions.
  • Stored procedures cannot be used in functions.
  • Functions can be used in stored procedures.
  • However, stored procedures cannot be used in stored procedures.
  • In fact, stored procedures can use stored procedures. For example, exec procedure1 can be used in procedure2, but it does not make a lot of sense.

Create a UDF

Used to calculate the product of a catalog

Alter function DBO. gettimebycatalog

(

@ Catvarchar (100)

)

Returns real

As

Begin

Declare @ timeall2 real

Set @ timeall2 = 1


Select @ timeall2 = @ timeall2 * cast (ID as float) from tableone

Where catalog = @ catalog

Return @ timeall2

End

Create a stored procedure
Alter procedure DBO. rocktest2


As


Begin





Create Table # temp2

(

Catalog varchar (100)

)


Insert into # temp2 (Catalog) Select distinct catalog from tableone


Select catalog, DBO. gettimebycatalog (Catalog)

From # temp2



End

In this way, we can get a Summary of the product data.

Of course, if you want to add restrictions,You can replace tableone in a function and stored procedure with a user-defined function with a table value.

If you still have a good solution, please advise 12.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.