Grouping to get the price - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping to get the price

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-03-23 : 10:58:56

I have a table looks like


Item                Price
a                       5
a                       6
a                       6
a                       4
b                       7
b                       7
b                       8
b                       8
c                       1
c                       2
c                       3
d                       2
d                       2
d                       2
d                       1

Here is the result I would like to get:


Result        Price
a                 6
b                 7
c                 1
d                 2

I would like to get the price that appears most times per each iteam.For example, price 6 is selected for item A because price 6 is shown twice whereas 5 and 4 are shown only one time.
However, if the price are shown same times, I would like to select the cheapest price. For example,
because both price 7 and 8 for item B are shown twice, I select the price 7 for item B.
and
If the prices are shown equally, I would like to select ,also, the cheapest price. For example, price 1 , 2, and 3 for item C are shown one time so that I select 1 as the price for item C.

Result
a 6
b 7
c 1
d 2

I would like to get the result using one select statement. How can I do this?

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-23 : 11:16:51

SQL does not have a built in mode function, so you would have to figure out the modal average for each one with a GROUPed COUNT and then use that as probably a joined query to figure the mode for each value.

-------
Moo. :)

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-03-23 : 11:53:34

Can you show me some example?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 12:04:30

I just did something like this...cut and paste the code into QA to see it run



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99([ID] int, VisitType varchar(15),  [Date] datetime)
GO

INSERT INTO myTable99([ID], VisitType, [Date])
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 2, 'Office', '2/5/2005' UNION ALL
SELECT 1, 'Office', '2/5/2005' UNION ALL
SELECT 3, 'Phone',  '2/5/2005' UNION ALL
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 2, 'Office', '2/5/2005' UNION ALL
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 1, 'Home',   '2/5/2005' UNION ALL
SELECT 2, 'Home',   '2/5/2005'
GO


SELECT VisitType, MAX(COUNT_VisitTypes) AS MAX_COUNT_VisitTypes
  FROM (
	SELECT [ID], VisitType, COUNT(*) AS COUNT_VisitTypes
	  FROM myTable99
	GROUP BY [ID], VisitType
) AS XXX
GROUP BY VisitType
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-23 : 12:43:30

your table will need a primary key on your table in order to do the calculations you need. As you may have learned by now, without a primary key on your tables there's not much you can do usefully with SQL.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 13:10:59

What do you plan to do with ties?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Item char(1), Price int)
GO

INSERT INTO myTable99(Item, Price)
SELECT 'a', 5 UNION ALL
SELECT 'a', 6 UNION ALL
SELECT 'a', 6 UNION ALL
SELECT 'a', 4 UNION ALL
SELECT 'b', 7 UNION ALL
SELECT 'b', 7 UNION ALL
SELECT 'b', 8 UNION ALL
SELECT 'b', 8 UNION ALL
SELECT 'c', 1 UNION ALL
SELECT 'c', 2 UNION ALL
SELECT 'c', 3 UNION ALL
SELECT 'd', 2 UNION ALL
SELECT 'd', 2 UNION ALL
SELECT 'd', 2 UNION ALL
SELECT 'd', 1
GO

   SELECT * 
     FROM ( SELECT xxx.Item, MAX(xxx.Count_Price) AS MAX_COUNT_Price 
  	      FROM (  SELECT Item, Price, COUNT(*) AS COUNT_Price
	    		FROM myTable99
		    GROUP BY Item, Price) AS XXX
	  GROUP BY Item) AS A
LEFT JOIN ( SELECT Item, Price, COUNT(*) AS COUNT_Price
	      FROM myTable99
	  GROUP BY Item, Price) AS B
       ON A.Item = B.Item
      AND A.MAX_COUNT_Price = B.COUNT_Price
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources