SQL How to count multiple columns with similar values

Question

I have a table with item sales for several stores, see sample table below.

  Store   Item   Price   Cost   Profit  
 ------- ------ ------- ------ -------- 
  ABC     Beer       5      3        2  
  ABC     Beer       5      3        2  
  ABC     Beer       4      3        1

I need to count items with similar prices, so result for above table should be similar to...

  Store   Item   Count   Price   Cost   Profit  
 ------- ------ ------- ------- ------ -------- 
  ABC     Beer       2       5      3        2  
  ABC     Beer       1       4      3        1

I tried following SQL query with no success...

SELECT Store,Item,Count(Price),Price,Cost,Profit
FROM Table
GROUP BY Store,Item,Price,Cost,Profit

Thoughts?

Your query is correct and is returning your expected result. What does it give you? — Gurwinder Singh
– Gurwinder Singh, Commented Jan 10, 2017 at 19:02
The query is correct. There must be some hidden character in your varchar columns because I have just now tested it in on my own system and produces the correct output. — Gurwinder Singh
– Gurwinder Singh, Commented Jan 10, 2017 at 19:16
Are you not actually trying to get a "count" of rows whose column values are similar? — JohnH
– JohnH, Commented Jan 10, 2017 at 19:54
Are any of those numbers you are grouping by a float? If they are, you will need to cast them as a decimal with an appropriate precision and scale to get this to work. — Dan Def
– Dan Def, Commented Jan 10, 2017 at 19:56

S3S · Accepted Answer · 2017-01-11 00:36:56Z

1

You state that I need to count items with similar prices. Given your sample data, your query works.

declare @table table (Store varchar(3), Item varchar(16), Price int, Cost int, Profit int)
insert into @table (Store, Item, Price, Cost, Profit) 
values

('ABC','Beer',5,3,2),
('ABC','Beer',5,3,2),  
('ABC','Beer',4,3,1)  

SELECT 
    Store,
    Item,
    Count(Price) as CT,
    Price,
    Cost,
    Profit
FROM @table
GROUP BY Store,Item,Price,Cost,Profit

However, if the Cost and Profit columns for each Store / Item / Count tuple is different, your grouping wouldn't show the results you want.

declare @table2 table (Store varchar(3), Item varchar(16), Price int, Cost int, Profit int)
insert into @table2 (Store, Item, Price, Cost, Profit) 
values

('ABC','Beer',5,3,2), --notice change in cost and profit
('ABC','Beer',5,2,1), --notice change in cost and profit 
('ABC','Beer',4,3,1)  

SELECT 
    Store,
    Item,
    Count(Price) as CT,
    Price,
    Cost,
    Profit
FROM @table2
GROUP BY Store,Item,Price,Cost,Profit

I assume in your real data, this is the case--that is your Cost and Profit are different. Thus, you need to remove those columns from the SELECT and GROUP BY in order to answer your question, I need to count items with similar prices. Additionaly, you may want to remove the Store column from the SELECT and GROUP BY as well.

SELECT 
    Store,
    Item,
    Count(Price) as CT,
    Price
FROM @table2
GROUP BY Store,Item,Price

edited Jan 11, 2017 at 0:36

answered Jan 10, 2017 at 20:46

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

neualex Over a year ago

Price may change, usually markdown or original price, cost never changes. I guess I have to make use 2 tables to get the results I need. First table, store/item/count/price, second table to include cost/profit.

S3S Over a year ago

@neualex normalizing your data as you are suggesting is usually a great idea

neualex Over a year ago

If I only have one store on the table such as ABC, the count on similar prices is correct for that only store, but if I had at least 3 different stores (S1, S2, S3) and they all have similar prices, how can I count similar prices per store? Same query counts similar prices for all stores, not per store. I can provide data if needed.

S3S Over a year ago

@neualex the last query does it per store. Removing store from the select and group by will give you the overall results. If this doesn't work I would open a NEW question and provide sample data and expected output so everyone can help out

Collectives™ on Stack Overflow

SQL How to count multiple columns with similar values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related