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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
or:
SELECT
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
FROM
dbo.ConsumerPricing
WHERE
DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.price,
dbo.ConsumerPricing.finishProductId
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
is above screenshot of As-Is or To-Be?
I guess this is what you want?
use this to get a historical change of the price vs dates
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
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
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
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
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
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
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
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.finish
, COUNT(DISTINCT dbo.ConsumerPricing.price)
FROM dbo.ConsumerPricing
WHERE DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.finish
SELECT
dbo.ConsumerPricing.finish
, COUNT(DISTINCT dbo.ConsumerPricing.price)
FROM dbo.ConsumerPricing
WHERE DATE >= '20150321'
GROUP BY
dbo.ConsumerPricing.finish
| finishProductId | |
|-----------------|---|
| 1 | 4 |
| 50 | 2 |
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.
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
| 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 |
Premium Content
You need an Expert Office subscription to comment.Start Free Trial