Link to home
Start Free TrialLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

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


User generated image
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

?
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

Avatar of Zolf

ASKER

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

Open in new window

Avatar of Zolf

ASKER

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

User generated image
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Zolf

ASKER

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

User generated image
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
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

User generated image
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

Paul,

Thanks for your feedback, your solution was perfect!!

User generated image
Avatar of Zolf

ASKER

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

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
Avatar of Zolf

ASKER

Paul,

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