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


1
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Zolf

8/22/2022 - Mon
Ryan Chong

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 Chong

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

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ryan Chong

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

Zolf

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

1
SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ryan Chong

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zolf

ASKER
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 Chong

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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Zolf

ASKER
Paul,

Thanks for your feedback, your solution was perfect!!

1
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Zolf

ASKER
cheers!!
PortletPaul

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
Zolf

ASKER
Paul,

Thanks so much for your feedbacks......it is so helpful!!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.