MSSQL COUNT with GROUP By

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
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

zolfAuthor 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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

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

1
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

zolfAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Paul,

Thanks for your feedback, your solution was perfect!!

1
zolfAuthor Commented:
cheers!!
PortletPaulEE Topic AdvisorCommented:
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
zolfAuthor Commented:
Paul,

Thanks so much for your feedbacks......it is so helpful!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.