MSSQL COUNT with GROUP By

tried:

SELECT
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.date,
    dbo.ConsumerPricing.finishProductId
FROM
    dbo.ConsumerPricing
WHERE
    DATE >= '20150321'
    GROUP BY
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.date,
    dbo.ConsumerPricing.finishProductId

Select allOpen in new window

?

or:

SELECT
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId
FROM
    dbo.ConsumerPricing
WHERE
    DATE >= '20150321'
    GROUP BY
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId

Select allOpen in new window

Thanks for your comment,but you see I want to count the number of times the prices has changed for a product in a given period. See my original query I need a Count as well.

ok... so try like this?

SELECT
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId,
    COUNT(dbo.ConsumerPricing.finishProductId) cnt
FROM
    dbo.ConsumerPricing
WHERE
    DATE >= '20150321'
    GROUP BY
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId

Select allOpen in new window

Please see shot,hope I made myself clearer as to what I am looking for.

ok... i think i got it, try this instead..

SELECT
    a.finishProductId,
    a.price,
    b.cnt
FROM
    dbo.ConsumerPricing a
    inner join (
        select b.finishProductId, count(distinct price) cnt
        from dbo.ConsumerPricing b
        group by b.finishProductId
    ) b
    on a.finishProductId = b.finishProductId
WHERE
    DATE >= '20150321'
Group By
a.finishProductId,
a.price,
b.cnt    

Select allOpen in new window

Thanks very much for your feedbacks, but it is not exactly what I am looking for.


You see this is the base of that query

SELECT
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId
FROM
    dbo.ConsumerPricing
WHERE
    DATE >= '20150321'
    GROUP BY
    dbo.ConsumerPricing.price,
    dbo.ConsumerPricing.finishProductId

Select allOpen in new window

only I need to have a count for the number of times the price changes. For e.g. for that finishProductId = 50 it should give me a column with count = 2

this is what I'm doing to test my scripts:

create table dbo.ConsumerPricing
(
  finishProductId int,
  price money,
  "date" varchar(8)
 );
 
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150322');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150323');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 40.15, '20150324');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 56.32, '20150325');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150326');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150327');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150328');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 14.22, '20150329');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 32.22, '20150330');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (1, 14.22, '20150331');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (50, 18000, '20150330');
insert into dbo.ConsumerPricing (finishProductId, price, "date") values (50, 20000, '20150331');

SELECT
    a.finishProductId,
    a.price,
    b.cnt
FROM
    dbo.ConsumerPricing a
    inner join (
        select b.finishProductId, count(distinct price) cnt
        from dbo.ConsumerPricing b
        group by b.finishProductId
    ) b
    on a.finishProductId = b.finishProductId
WHERE
    DATE >= '20150321'
Group By
a.finishProductId,
a.price,
b.cnt 

Select allOpen in new window

Paul,

Thanks for your feedback, your solution was perfect!!

cheers!!

Zolf

It should be noted that the original question was about display of dates, as well as the count. My post (ID: 40990532) only addressed the count.

Ryan Chong at ID: 40990410 (& before my post) is also using COUNT(DISTINCT ... plus addressing other items raised.

i.e. Ryan was trying to solve additional parts of the overall question.

I was working on some of those added items too, and arrived at this (untested) solution that  I think will allow display of dates AND produce the wanted count.

;with CTE as (
              select *, row_number() over(partition by finishProductId, price
                                          order by [date] DESC ) as rn
              from dbo.ConsumerPricing
              where [date] >= '20150321'
             )
select
*
from CTE
cross apply (
            select COUNT(DISTINCT ref2.price)
            from CTE as ref2
            where ref2.finishProductId = cte.finishProductId
            ) oa (num_prices)
where rn = 1

Select allOpen in new window

The date that is displayed is the "most recent" for each product/price combination, the count is the number of times the price has changed (not just for that date) e.g.

| finishProductId | price |     date | rn | num_prices |
|-----------------|-------|----------|----|------------|
|               1 | 14.22 | 20150331 |  1 |          4 |
|               1 | 32.22 | 20150330 |  1 |          4 |
|               1 | 40.15 | 20150324 |  1 |          4 |
|               1 | 56.32 | 20150325 |  1 |          4 |
|              50 | 18000 | 20150330 |  1 |          2 |
|              50 | 20000 | 20150331 |  1 |          2 |

Select allOpen in new window

also see http://sqlfiddle.com/#!6/eab0d/7

Paul,

Thanks so much for your feedbacks......it is so helpful!!