Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trouble with self learning SQL

Author  Topic 

AJK1888
Starting Member

6 Posts

Posted - 2013-06-10 : 06:31:25
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
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
Go to Top of Page

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

52326 Posts

Posted - 2013-06-10 : 07:56:41
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

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 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 08:45:09
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
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 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
Go to Top of Page

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 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 09:14:45
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
Go to Top of Page

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

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

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

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 s
where p.prod_id=s.prod_id



SHAIK
Go to Top of Page

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

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

- Advertisement -