June 2, 2011 at 5:02 am
_______________________
AVAILABLE DATA:Attached screenshot + description
______________________________________
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
______________________________________________
AND MY QUESTION IS :
________________________________
For each maker find out the average screen size of the laptops produced by it.
Result set: maker, average screen size.
_____________________________________
AND THE QUERY THAT I AM USING IS:
________________________________________
SELECT P.MAKER,AVG(L.SCREEN)FROM PRODUCT P
INNER JOIN LAPTOP L
ON P.MODEL = L.MODEL GROUP BY P.MODEL
WHERE P.MAKER = 'LAPTOP'
i am getting error :Incorrect syntax near the keyword 'WHERE'."
June 2, 2011 at 5:16 am
Regarding the syntax error: WHERE needs to be placed before GROUP BY
SELECT P.MAKER,AVG(L.SCREEN) AS AliasColumnNameGoesHere
FROM PRODUCT P
INNER JOIN LAPTOP L
ON P.MODEL = L.MODEL
WHERE P.MAKER = 'LAPTOP'
GROUP BY P.MODEL
But I'd also use a different table design. I'm not sure if I would have separate tables for all three types. But most definitely not for PC and Laptop. Both are computers. What are you going to do if you need to add tablet PCs and/or iPads? Is a tablet PC a laptop or a PC or even a new table? What category will a scanner or a fax go to? And how about a multi-functional device that can print, scan and fax? To me all those are devices....
June 2, 2011 at 5:26 am
Hi LutzM,
I used the query suggested by you and error mesaage is "Column 'PRODUCT.maker' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
We just need Product and Laptop table ..Please check the structure of table
Table is like this:
_________________________________________
1) Product:
maker model type
A 1232 PC
A 1233 PC
A 1276 Printer
A 1298 Laptop
A 1401 Printer
A 1408 Printer
A 1752 Laptop
B 1121 PC
B 1750 Laptop
C 1321 Laptop
D 1288 Printer
D 1433 Printer
E 1260 PC
E 1434 Printer
E 2112 PC
E 2113 PC
_________________________________
2)Laptop
____________________________
code model speed ram hd price screen
1 1298 350 32 4.0 700.0000 11
2 1321 500 64 8.0 970.0000 12
3 1750 750 128 12.0 1200.0000 14
4 1298 600 64 10.0 1050.0000 15
5 1752 750 128 10.0 1150.0000 14
6 1298 450 64 10.0 950.0000 12
_______________________________
3) PC
______________________
code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
12 1233 800 128 20.0 50x 970.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20.0 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
8 1232 450 64 8.0 24x 350.0000
9 1232 450 32 10.0 24x 350.0000
____________________________
4) Printer
____________
codemodelcolortypeprice
11276nLaser400.0000
21433yJet270.0000
31434yJet290.0000
41401nMatrix150.0000
51408nMatrix270.0000
61288nLaser400.000
__________________
June 2, 2011 at 5:32 am
Use the P.MAKER in Group by or P.MODEL in Select list..
try eitherways 🙂
June 2, 2011 at 5:33 am
Arrgghhh!!!
I'm sorry. I didn't look at the original query close enough to spt that it is grouped by a different column than used in the SELECt list.
Here's the revised version:
SELECT P.MAKER, AVG(L.SCREEN) AS AliasColumnNameGoesHere
FROM PRODUCT P
INNER JOIN LAPTOP L
ON P.MODEL = L.MODEL
WHERE P.MAKER = 'LAPTOP'
GROUP BY P.MAKER
Regarding your question "We just need Product and Laptop table": What do you mean?
Please post table def and sampel data in a ready to use format as described in the first link in my signature. It would help a lot to understand what you're looking for and it would also reduce the time for us to create a test environment.
June 3, 2011 at 2:45 am
_______________________
AVAILABLE DATA:Attached screenshot + description
______________________________________
The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.
and tables are :
1) Product:
maker model type
A 1232 PC
A 1233 PC
A 1276 Printer
A 1298 Laptop
A 1401 Printer
A 1408 Printer
A 1752 Laptop
B 1121 PC
B 1750 Laptop
C 1321 Laptop
D 1288 Printer
D 1433 Printer
E 1260 PC
E 1434 Printer
E 2112 PC
E 2113 PC
_________________________________
2)Laptop
____________________________
code model speed ram hd price screen
1 1298 350 32 4.0 700.0000 11
2 1321 500 64 8.0 970.0000 12
3 1750 750 128 12.0 1200.0000 14
4 1298 600 64 10.0 1050.0000 15
5 1752 750 128 10.0 1150.0000 14
6 1298 450 64 10.0 950.0000 12
_______________________________
3) PC
______________________
code model speed ram hd cd price
1 1232 500 64 5.0 12x 600.0000
10 1260 500 32 10.0 12x 350.0000
11 1233 900 128 40.0 40x 980.0000
12 1233 800 128 20.0 50x 970.0000
2 1121 750 128 14.0 40x 850.0000
3 1233 500 64 5.0 12x 600.0000
4 1121 600 128 14.0 40x 850.0000
5 1121 600 128 8.0 40x 850.0000
6 1233 750 128 20.0 50x 950.0000
7 1232 500 32 10.0 12x 400.0000
8 1232 450 64 8.0 24x 350.0000
9 1232 450 32 10.0 24x 350.0000
____________________________
4) Printer
____________
code model color type price
1 1276 n Laser 400.0000
2 1433 y Jet 270.0000
3 1434 y Jet 290.0000
4 1401 n Matrix 150.0000
5 1408 n Matrix 270.0000
6 1288 n Laser 400.000
__________________
______________________________________________
AND MY QUESTION IS :
Find the makers producing at least three distinct models of PCs.
Result set: maker, number of models
__________AND MY QUERY IS (BUT ITS NOT RUNNING) _________________________________
SELECT model, COUNT(model) AS Qty_model, TYPE FROM PRODUCT
GROUP BY MODEL
HAVING COUNT(MODEL)>=3
AND TYPE = 'PC'
_____________ERROR MESSAGE IS________________
Column 'PRODUCT.type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 3, 2011 at 3:04 am
Either
SELECT model, COUNT(model) AS Qty_model
FROM PRODUCT
GROUP BY MODEL
HAVING COUNT(MODEL)>=3
AND TYPE = 'PC'
or
SELECT model, COUNT(model) AS Qty_model, TYPE
FROM PRODUCT
GROUP BY MODEL, TYPE
HAVING COUNT(MODEL)>=3
AND TYPE = 'PC'
June 8, 2011 at 2:23 am
Hi ,
Please help me on this:(Check my first post for the table schema)
For each value of PC speed that exceeds 600 MHz, define the average price of the PCs with identical speeds.
June 8, 2011 at 2:24 am
PC table
codemodelspeedramhdcdprice
1123250064512x600
101260500321012x350
1112339001284040x980
1212338001282050x970
211217501281440x850
3123350064512x600
411216001281440x850
51121600128840x850
612337501282050x950
71232500321012x400
8123245064824x350
91232450321024x350
June 8, 2011 at 2:33 am
I USED THIS QUERY AND THIS IS RETURNING CORRECT FOR ALL EXCEPT 750 BUT I WANT AVERAGE FOR 750 ALSO...(like it has to be 850+950/2 = 900)
________________________________________________________________________
SELECT PRICE,AVG(PRICE) as AVG_PRICE ,SPEED FROM PC GROUP BY PRICE,SPEED
HAVING SPEED>600
PRICE AVG_PRICE SPEED
850. 850 750
950 950 750
970 970 800
980 980 900
_________________________________________________________________________
AND I need this output:
speed Avg_price
750 900
800 970
900 980
__________________________________________________
June 8, 2011 at 3:09 pm
Tiya (6/8/2011)
I USED THIS QUERY AND THIS IS RETURNING CORRECT FOR ALL EXCEPT 750 BUT I WANT AVERAGE FOR 750 ALSO...(like it has to be 850+950/2 = 900)
________________________________________________________________________
SELECT PRICE,AVG(PRICE) as AVG_PRICE ,SPEED FROM PC GROUP BY PRICE,SPEED
HAVING SPEED>600
PRICE AVG_PRICE SPEED
850. 850 750
950 950 750
970 970 800
980 980 900
_________________________________________________________________________
AND I need this output:
speed Avg_price
750 900
800 970
900 980
__________________________________________________
This screams of homework but...why do you have Price in your select if you don't want it in the output? All you want is the speed and the average price for that speed so only include those columns in your select.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2011 at 9:08 pm
even if price is coming that doesnt matter but my objective is to find average of 750 speeed.that is 850+950/2= 900
June 9, 2011 at 7:01 am
Tiya (6/8/2011)
even if price is coming that doesnt matter but my objective is to find average of 750 speeed.that is 850+950/2= 900
Like I said remove price from your query and you should be there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2012 at 9:22 am
Hi there
please try this query :
select speed,avg(price)
from pc
where speed >600
group by speed
Regards
Kaushal
June 4, 2012 at 11:56 pm
Sorry for the late reply. Just worked your Requirements and came up with the following Queries:
--Query For First Requirement
Select a.Maker, AVG(b.Screen) As Average_ScreenSize From Product As a
JOIN Laptop As b On a.Model = b.Model
Group By a.Maker
--Query For Second Requirement
Select Maker, SUM(Case When Product_type = 'PC' Then 1 Else 0 End) As Number_of_Models From Product
Group By Maker
Having SUM(Case When Product_type = 'PC' Then 1 Else 0 End) >= 3
--Query For Third Requirement
Select Speed, AVG(Price) As AveragePrice
From
(Select * From PC Where Speed > 600) As a
Group By Speed
Hope they help.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply