SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trouble with self learning SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  06:31:25  Show Profile  Reply with Quote
need command for query

display 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_id
prod_name
price
on_hand
supp_id

Sales;
cust_id
prod_id
quantity smallint
date_of_sale

Customers;
cust_id
name
phone

Suppliers;
supp_id
company_name
town
phone

The tables are populated with appropriate data regarding the Printer machines.

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/10/2013 :  07:22:48  Show Profile  Reply with Quote
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
Go to Top of Page

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  07:52:08  Show Profile  Reply with Quote
SELECT Sum (Price) as “total value”,
Prod_id as “product id”,
Prod_name as “product name”,
From Products
Where prod_id like ‘A1%’
Order by price DESC

I have tried the above but i am unsure if this is the correct code.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/10/2013 :  07:56:41  Show Profile  Reply with Quote
it just require a simple GROUP BY and filter based on pattern match using LIKE

have a look at syntax of GROUP BY and LIKE in BOL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  08:40:49  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

it just require a simple GROUP BY and filter based on pattern match using LIKE

have a look at syntax of GROUP BY and LIKE in BOL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Still do not understand how to use the group command.
can you please tell me how.


Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/10/2013 :  08:45:09  Show Profile  Reply with Quote
This is basic query...
Refer this link.. you can get idea to implement that query
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185501

--
Chandu
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/10/2013 :  08:47:55  Show Profile  Reply with Quote
quote:
Originally posted by AJK1888

quote:
Originally posted by visakh16

it just require a simple GROUP BY and filter based on pattern match using LIKE

have a look at syntax of GROUP BY and LIKE in BOL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Still 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.asp



Cheers
MIK

Edited by - MIK_2008 on 06/10/2013 08:51:50
Go to Top of Page

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  09:03:58  Show Profile  Reply with Quote
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 LIKE

have a look at syntax of GROUP BY and LIKE in BOL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Still 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.asp



Cheers
MIK



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 Products
Where prod_id like ‘A1%’
Order by price DESC
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/10/2013 :  09:14:45  Show Profile  Reply with Quote
quote:
Originally posted by AJK1888
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 Products
Where prod_id like ‘A1%’
Order by price DESC


It gives syntax error...

Non-aggregate column in the SELECT Clause must be in GROUP BY Clause
SELECT Sum (Price) as “total value”, -- Total Sales means Quantity * Price
Prod_id as “product id”,
Prod_name as “product name”,
From Products
Where prod_id like ‘A1%’
GROUP BY Prod_id, Prod_name
Order by price DESC

For that Total Value column, you should join Sales table with Products Table and then apply as follows:
SUM( Price* Quantity) "Total Value"

--
Chandu

Edited by - bandi on 06/10/2013 09:15:07
Go to Top of Page

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  09:53:58  Show Profile  Reply with Quote
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 table

i 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
Go to Top of Page

AJK1888
Starting Member

United Kingdom
6 Posts

Posted - 06/10/2013 :  09:53:59  Show Profile  Reply with Quote
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 table

i 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/10/2013 :  10:14:40  Show Profile  Reply with Quote
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 table

i 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 tables


SELECT prod_id AS "Product",prod_name AS "Product Name",Date_of_sale AS "Date of Sale",
FROM Products p
INNER JOIN Sales s
ON s.somecolumn = p.somerelatedcolumn


makesure you substitute the relevant columns in part in blue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/10/2013 10:17:26
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/11/2013 :  01:22:17  Show Profile  Reply with Quote
SELECT p.prod_id AS [Product],
p.prod_name AS [Product Name],
s.Date_of_sale AS [Date of Sale],
FROM Products p,Sales s
where p.prod_id=s.prod_id



SHAIK
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/11/2013 :  01:32:04  Show Profile  Reply with Quote
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 table

i 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 SQL
http://www.w3schools.com/sql/default.asp
-- Joins
http://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

Edited by - bandi on 06/11/2013 01:33:57
Go to Top of Page

Rajan Sahai
Starting Member

India
8 Posts

Posted - 06/12/2013 :  11:54:21  Show Profile  Reply with Quote
To know how to join three or more tables refer the link below.

unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000