October 9, 2020 at 6:42 pm
Hi:
Need help with group by SQL query. I want the select to display all columns, but the group by should be on only few columns
create table #tbl1
(
IDate date,
PID varchar(20),
SID varchar(20),
BTot float,
TQty float,
Price float,
Result varchar(10)
)
insert into #tbl1 values ('10/15/2019','4567','ABC',103.24,4567.34,NULL,'Int')
insert into #tbl1 values ('10/15/2019','4567','ABC',102.54,5678.34,NULL,'Int')
insert into #tbl1 values ('10/15/2019','3456','ABC',202.24,3494.56,NULL,'Ter')
insert into #tbl1 values ('10/15/2019','3456','TMZ',56.24,2323.98,NULL,'Int')
insert into #tbl1 values ('10/15/2019','3456','TMZ',24.23,234.78,NULL,'Int')
insert into #tbl1 values ('10/15/2019','1358','PQR',50.25,2896.24,NULL,'Ter')
insert into #tbl1 values ('10/15/2019','1358','PQR',324.24,2345.23,NULL,'Ter')
select IDate,PID,SID, SUM(BTot) AS Total, SUM(TQty) AS TQty, SUM(BTot)/SUM(TQty) AS Price, Result
from #tbl1
Group by IDate,PID,SID,Result
In this particular case, I do not want the Group by on 'Result' column. If I remove that it throws error.
I also tried with using OVER PARTITION, but that also fails.
select IDate,PID,SID,
SUM(BTot) OVER(PARTITION BY IDate,PID,SID) AS Total,
SUM(TQty) OVER(PARTITION BY IDate,PID,SID) AS TQty,
SUM(BTot)/SUM(TQty) OVER(PARTITION BY IDate,PID,SID) AS Price,
Result
from #tbl1
Thanks!
October 9, 2020 at 7:32 pm
If there are multiple different values for 'result' in any particular grouping, which one of those would you like to return?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 14, 2020 at 4:08 pm
If you are interested in returning all the values of Result and there are only two possible results, return Min(Result) and Max(Result). If there are more than 2 possible Result values I would use an inline view of the group by and then join the group by columns of the inline view to the same columns in #tlb1. For each group you will return all the rows (7 rows in your example) with the same totals so this may not be what you are looking for.
SELECT
tab.idate,
tab.pid,
tab.sid,
gb.total,
gb.tqty,
gb.price,
gb.result
FROM ( SELECT
idate,
pid,
sid,
SUM( btot ) AS total,
SUM( tqty ) AS tqty,
SUM( btot )/ SUM( tqty ) AS price
FROM #tbl1
GROUP BY
idate,
pid,
sid) gb
JOIN #tbl1 tab
ON tab.idate = gb.idate
AND tab.pid = gb.pid
AND tab.sid = gb.sid
ORDER BY tab.idate,
tab.pid,
tab.sid,
gb.result
You have a syntax error in your price column when using over, which is the best way to craft this query. You have to use two separate over clauses in the calculation. A great way to debug this kind of error is to comment out the select clauses with calculations and gradually add them back until you find the problem and then solve that problem.
SELECT
idate,
pid,
sid,
SUM( btot )
OVER( PARTITION BY idate,
pid,
sid ) AS total,
SUM( tqty )
OVER( PARTITION BY idate,
pid,
sid ) AS tqty,
SUM( btot ) OVER( PARTITION BY idate,
pid,
sid )
/
SUM( tqty )
OVER( PARTITION BY idate,
pid,
sid ) AS price,
result
FROM
#tbl1;
As an aside, avoid the inaccurate float for finite quantities and amounts. Float is useful for scientific measurements that are inaccurate due to the precision of the measurements. Finite amounts stored as float, when used in calculations, will result in rounding errors that will be hard to track down and using the round function will work sometimes and other times not. This is most obvious when dealing with money and customers want accounts accurate to the nearest penny and a 1 penny difference is not acceptable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply