MSSQL COUNT with GROUP By

zolf
zolf used Ask the Experts™
on
Hello there,

I am trying to get the product prices with its date that have changed over a period. I have problem displaying its dates also. I am able to count the products price chages over a persion but cannot get its dates also. How can I do this. Below is my query

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

Open in new window


1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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

Open in new window

?
Ryan ChongSoftware Team Lead

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

Open in new window

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

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

Open in new window

Author

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

1
Ryan ChongSoftware Team Lead
Commented:
is above screenshot of As-Is or To-Be?

I guess this is what you want?

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

Open in new window


use this to get a historical change of the price vs dates

  SELECT
    a.finishProductId,
    a.date,
    a.price, b.price prevPrice,
    case when b.price is null then 0 else a.price-b.price end diff
FROM
    dbo.ConsumerPricing a
    left join dbo.ConsumerPricing b
    on a.finishProductId = b.finishProductId
    and b.date = (
        select max(c.date) from dbo.ConsumerPricing c
        where c.finishProductId = b.finishProductId and c.date < a.date
    )
WHERE
    a.date >= '20150321' and (b.price is null or case when b.price is null then 0 else a.price-b.price end <> 0)
order by a.date

Open in new window

Ryan ChongSoftware Team Lead

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

Open in new window

Author

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

1
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

Open 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
Ryan ChongSoftware Team Lead

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

Open in new window

test results
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I think you want  COUNT(DISTINCT ....) which is NOT the count of all rows, but just the number of times the price has changed.

SELECT
      dbo.ConsumerPricing.finishProductId
   , COUNT(DISTINCT dbo.ConsumerPricing.price)
FROM dbo.ConsumerPricing
WHERE DATE >= '20150321'
GROUP BY
      dbo.ConsumerPricing.finishProductId
| finishProductId |   |
|-----------------|---|
|               1 | 4 |
|              50 | 2 |

Open in new window

Author

Commented:
Paul,

Thanks for your feedback, your solution was perfect!!

1

Author

Commented:
cheers!!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

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

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

Open in new window

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

Author

Commented:
Paul,

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial