Author |
Topic |
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 06:31:25
|
need command for querydisplay the product ID, the product name, nad the total value of sales for each of the 'All-in-one' printers in stock. These are indentified by having 'A1' in positions one and two of the product ID. Use suitable headings for the columns. Sort the data into decending order of total value for each 'All-in-one' Printer.There are four tables;Products, Sales, Customers, Suppliers containing the following fields;Products; prod_idprod_nameprice on_handsupp_idSales;cust_idprod_idquantity smallintdate_of_saleCustomers;cust_idnamephoneSuppliers;supp_idcompany_nametownphone The tables are populated with appropriate data regarding the Printer machines. |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-10 : 07:22:48
|
This is simple query...What you tried as of now...? Show us the query which u tried and let us know the problem where you are facing?--Chandu |
|
|
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 07:52:08
|
SELECT Sum (Price) as “total value”,Prod_id as “product id”,Prod_name as “product name”, From ProductsWhere prod_id like ‘A1%’ Order by price DESCI have tried the above but i am unsure if this is the correct code. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-10 : 07:56:41
|
it just require a simple GROUP BY and filter based on pattern match using LIKEhave a look at syntax of GROUP BY and LIKE in BOL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 08:40:49
|
quote: Originally posted by visakh16 it just require a simple GROUP BY and filter based on pattern match using LIKEhave a look at syntax of GROUP BY and LIKE in BOL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogsStill do not understand how to use the group command. can you please tell me how.
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-06-10 : 08:47:55
|
quote: Originally posted by AJK1888
quote: Originally posted by visakh16 it just require a simple GROUP BY and filter based on pattern match using LIKEhave a look at syntax of GROUP BY and LIKE in BOL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogsStill do not understand how to use the group command. can you please tell me how.
Refer to the link for how to use Group By clause ... samples are provided in the link. http://www.w3schools.com/sql/sql_groupby.aspCheersMIK |
|
|
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 09:03:58
|
quote: Originally posted by MIK_2008
quote: Originally posted by AJK1888
quote: Originally posted by visakh16 it just require a simple GROUP BY and filter based on pattern match using LIKEhave a look at syntax of GROUP BY and LIKE in BOL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogsStill do not understand how to use the group command. can you please tell me how.
Refer to the link for how to use Group By clause ... samples are provided in the link. http://www.w3schools.com/sql/sql_groupby.aspCheersMIK
Don't think its a GROUP used this still not working, any idea why?SELECT Sum (Price) as “total value”,Prod_id as “product id”,Prod_name as “product name”, From ProductsWhere prod_id like ‘A1%’ Order by price DESC |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-10 : 09:14:45
|
quote: Originally posted by AJK1888Don't think its a GROUP used this still not working, any idea why?SELECT Sum (Price) as “total value”,Prod_id as “product id”,Prod_name as “product name”, From ProductsWhere prod_id like ‘A1%’ Order by price DESC
It gives syntax error...Non-aggregate column in the SELECT Clause must be in GROUP BY ClauseSELECT Sum (Price) as “total value”, -- Total Sales means Quantity * PriceProd_id as “product id”,Prod_name as “product name”, From ProductsWhere prod_id like ‘A1%’ GROUP BY Prod_id, Prod_name Order by price DESCFor that Total Value column, you should join Sales table with Products Table and then apply as follows:SUM( Price* Quantity) "Total Value"--Chandu |
|
|
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 09:53:58
|
okay thankyou now i am trying to do this,Display product id, product name and the date of sale. i need to get these details from products table and sales tablei have tried this but not working SELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",FROM Products,Sales |
|
|
AJK1888
Starting Member
6 Posts |
Posted - 2013-06-10 : 09:53:59
|
okay thankyou now i am trying to do this,Display product id, product name and the date of sale. i need to get these details from products table and sales tablei have tried this but not working SELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",FROM Products,Sales |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-10 : 10:14:40
|
quote: Originally posted by AJK1888 okay thankyou now i am trying to do this,Display product id, product name and the date of sale. i need to get these details from products table and sales tablei have tried this but not working SELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",FROM Products,Sales
you've not specified relationship between tablesSELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",FROM Products pINNER JOIN Sales sON s.somecolumn = p.somerelatedcolumn makesure you substitute the relevant columns in part in blue------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-11 : 01:22:17
|
SELECT p.prod_id AS [Product],p.prod_name AS [Product Name],s.Date_of_sale AS [Date of Sale],FROM Products p,Sales swhere p.prod_id=s.prod_idSHAIK |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-11 : 01:32:04
|
quote: Originally posted by AJK1888 okay thankyou now i am trying to do this,Display product id, product name and the date of sale. i need to get these details from products table and sales tablei have tried this but not working SELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",FROM Products,Sales
Hi,Try to learn the technology....You can get basic knowledge of SQL by using following links:-- Basic clauses in the SQLhttp://www.w3schools.com/sql/default.asp -- Joinshttp://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/http://blog.sqlauthority.com/2011/10/04/sql-server-quick-note-about-join-common-questions-and-simple-answers/--Chandu |
|
|
Rajan Sahai
Starting Member
8 Posts |
Posted - 2013-06-12 : 11:54:21
|
To know how to join three or more tables refer the link below.unspammed |
|
|
|