What's wrong with this SQL?

jonesong · December 30, 2015, 8:31am

Select distinct
a.[register] as [Terminal]
,format(a.[open_date],'yyyy-MM-dd') as [Date]
,a.[open_birno] as [Beg Invoice]
,a.[clos_birno] as [Ending Invoice]
,isnull(d.rd_tamount,0)
,isnull(c.rd_tamount,0)
,isnull(e.rd_tamount,0)
,isnull(f.rd_tamount,0)
,isnull(g.rd_tamount,0)
,isnull(h.rd_tamount,0)
,isnull(i.rd_tamount,0)
,isnull(j.rd_tamount,0)
,isnull(k.rd_tamount,0)
,isnull(l.rd_tamount,0)
,isnull(m.rd_tamount,0)
,isnull(n.rd_tamount,0)
,isnull(o.rd_tamount,0)
,isnull(p.rd_tamount,0)
,isnull(q.rd_tamount,0)
,isnull(b.rd_tamount,0)

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.open_date = dbo.grouptodate(b.rd_group)
and (b.rd_type='M' and b.rd_code='VE')
left join dbo.regdtail c on a.open_date = dbo.grouptodate(c.rd_group)
and (c.rd_type='T' and c.rd_code ='15')
left join dbo.regdtail d on a.open_date = dbo.grouptodate(d.rd_group)
and (d.rd_type='T' and d.rd_code ='0')
left join dbo.regdtail e on a.open_date = dbo.grouptodate(e.rd_group)
and (e.rd_type='T' and e.rd_code ='23')
left join dbo.regdtail f on a.open_date = dbo.grouptodate(f.rd_group)
and (f.rd_type='T' and f.rd_code ='28')
left join dbo.regdtail g on a.open_date = dbo.grouptodate(g.rd_group)
and (g.rd_type='T' and g.rd_code ='43')
left join dbo.regdtail h on a.open_date = dbo.grouptodate(h.rd_group)
and (h.rd_type='T' and h.rd_code ='35')
left join dbo.regdtail i on a.open_date = dbo.grouptodate(i.rd_group)
and (i.rd_type='T' and i.rd_code ='17')
left join dbo.regdtail j on a.open_date = dbo.grouptodate(j.rd_group)
and (j.rd_type='T' and j.rd_code ='42')
left join dbo.regdtail k on a.open_date = dbo.grouptodate(k.rd_group)
and (k.rd_type='D' and k.rd_code ='A1')
left join dbo.regdtail l on a.open_date = dbo.grouptodate(l.rd_group)
and (l.rd_type='D' and l.rd_code ='05')
left join dbo.regdtail m on a.open_date = dbo.grouptodate(m.rd_group)
and (m.rd_type='D' and m.rd_code ='A2')
left join dbo.regdtail n on a.open_date = dbo.grouptodate(n.rd_group)
and (n.rd_type='D' and n.rd_code ='02')
left join dbo.regdtail o on a.open_date = dbo.grouptodate(o.rd_group)
and (o.rd_type='D' and o.rd_code ='10')
left join dbo.regdtail p on a.open_date = dbo.grouptodate(p.rd_group)
and (p.rd_type='D' and p.rd_code ='11')
left join dbo.regdtail q on a.open_date = dbo.grouptodate(q.rd_group)
and (q.rd_type='T' and q.rd_code ='26')

where a.register = 'A5'

and a.open_date between '2015-06-04' and '2015-06-04'
group by a.[register], a.[open_date], a.[open_birno], a.[clos_birno]
, b.rd_tamount, c.rd_tamount, d.rd_tamount
, e.rd_tamount, f.rd_tamount, g.rd_tamount
, h.rd_tamount, i.rd_tamount, j.rd_tamount
, k.rd_tamount, l.rd_tamount, m.rd_tamount
, n.rd_tamount, o.rd_tamount, p.rd_tamount
, q.rd_tamount

Muj9 · December 30, 2015, 12:11pm

What error do you get?

jonesong · December 31, 2015, 2:39am

Hello, i got multiple rows by dates. For every dates ( Dec 28,29,30), there were many rows in it. But the only right data is the last row record of the specific date.

How can i trim the rows / how can i just only get the last row record of each date?

Thanks

bitsmed · December 31, 2015, 12:39pm

Please provide description of tables (as create statements), sample data (as insert statements) end your expected output (from the sample data).

jonesong · January 1, 2016, 8:46am

Hi guys,

After rethinking my query, I changed it to make the process faster.

Pic A is executed successfully. But Pic B, how will it be fix?

bitsmed · January 1, 2016, 4:13pm

Please provide description of tables (as create statements), sample data (as insert statements) end your expected output (from the sample data).

JeffModen · January 1, 2016, 11:22pm

The error clearly states what the problem is. Remove the aggregate from the a.Open_Date column in the SELECT list.

I also strongly suggest that you NOT use FORMAT in any code. It's quite literally 44 times slower than CONVERT.

Also, if you want better help, please stop posting pictures of code. Always post real code and learn to use thee "preformatted text" option in the iconic menu bar when prepping a message. That way, we can copy and paste your code to give you coded answers back.

jonesong · January 2, 2016, 12:58am

Thank you for the time guys. I apologize for the screenshots. I'm new in databases but willing to learn.

success query =
isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=1 then b.rd_tamount end),0) as [Line Disc%]

aggregate function on an expression containing an aggregate or a subquery query=
isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code='02' then b.rd_ttrans end)
then b.rd_tamount end),0) as [Line Disc%]

The bold text is the changes between the two queries. I used the Case to get the max of b.rd_ttrans, but the error is aggregate function on an expression containing an aggregate or a subquery query.

How can I get the max of b.rd_ttrans?

jonesong · January 4, 2016, 6:06am

CREATE TABLE [dbo].[REGDTAIL2](
[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('T','15',2.00,860.98),
('T','15',1.00,500.00),
('T','15',1.00,360.98)

Select distinct

max(Case when a.rd_type='T' and a.rd_code='15' and a.rd_ttrans=

max(Case when a.rd_type='T' and a.rd_code='15' then a.rd_ttrans end)

then a.rd_tamount end) as [Zero Amount]

FROM [dbo].[REGDTAIL2] a

Error Msg =
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I think its because of the bold text in the select query...if i just replace the bold text like 1 or 2, it's fine. But I wanted to get the max value of the a.rd_ttrans...

khtan · January 4, 2016, 6:18am

maybe this is what you want ? If it is not, please post your required result

; with 
cte as
(
	select	*, rn  = row_number() over (order by rd_tamount desc)
	from	[dbo].[REGDTAIL2]
)
select	*
from	cte
where	rn	= 1

jonesong · January 4, 2016, 6:59am

Hi Khtan,

I tried ur advice. But. I have other table joining them and different types of value in the rd_type and rd_code. What I need is the result of (T,15,2,860.98). How can I have a condition within a condition?
e.g. (Case when a.rd_type='T' and a.rd_code='15' and a.rd_ttrans=
max(Case when a.rd_type='T' and a.rd_code='15' then a.rd_ttrans end)
then a.rd_tamount end)

the rd_type and rd_code has different types, while the rd_ttrans, i just want to get the highest no of it. if it inserts more records, it looks like this...
INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('T','10',2.00,60.00),
('T','10',1.00,50.00),
('D','1',1.00,36.00),
('D','1',2.00,100.00),
('D','1',3.00,200.00)

the result I need is
T, 15, 2, 860.98
T, 10, 2, 60.00
D, 1, 3, 200

the records of rd_ttrans with less than the highest value are useless record.

khtan · January 4, 2016, 7:02am

change to

OVER ( PARTITION BY rd_type, rd_code ORDER BY rd_tamount desc)

jonesong · January 4, 2016, 7:15am

Thanks Khan, it worked!

How can I use your advice if i have this query?

Select distinct
max(a.[register]) as [Terminal]
,isnull(max(Case when b.rd_type='D' and b.rd_code ='A2' then b.rd_tamount end),0) as [Cash]
,isnull(max(Case when b.rd_type='D' and b.rd_code ='02' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code ='02' then b.rd_ttrans end) then b.rd_tamount end),0) as [Line Disc%]

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.open_date = dbo.grouptodate(b.rd_group)
where a.open_date between '2015-10-10' and '2015-10-11'
group by a.[open_date]

khtan · January 4, 2016, 8:27am

what are you trying to achieve with this query ?

jonesong · January 4, 2016, 8:32am

the second select statement is to get the value in the records where rd_type='D' and rd_code='02' and rd_ttrans= highest value of it. I joined the table with other data I needed where date is the common value of it.

khtan · January 4, 2016, 1:57pm

please post some sample data from both the table and the expected result.

also what does grouptodate() do ?

jonesong · January 5, 2016, 5:39am

Hi Khtan,

okay, here the data

CREATE TABLE [dbo].[REGDTAIL2](
[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
([rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('D','1',1.00,36.00),
('D','1',2.00,100.00),
('D','1',3.00,200.00),
('D','1',4.00,150.00)

In these records, rd_type=D and rd_code=1 has four records, but the rd_ttrans=4 has the amount of 150.00. so the result i want is rd_tamount = 150.00.

my rd_ttrans has the condition of getting the max of rd_ttrans so that i can get the rd_tamount.

here is my query
Select distinct

max(Case when a.rd_type='D' and a.rd_code='1' and a.rd_ttrans=

(max(Case when rd_type='D' and rd_code='1' then rd_ttrans end))

then a.rd_tamount end) as [Zero Amount]

FROM [dbo].[REGDTAIL2] a

this query has error of "cannot perform an aggregate function on a expression or subquery"

i'm using mySql 2014

khtan · January 5, 2016, 5:46am

it is actually same with the previous query that i posted. Just change to ORDER BY rd_ttrans

; with 
cte as
(
	select	*, rn  = row_number() OVER ( PARTITION BY rd_type, rd_code 
                                                 ORDER BY rd_ttrans desc)
	from	[dbo].[REGDTAIL2]
)
select	*
from	cte
where	rn	= 1

Well, this site is for MS SQL Server. My query should works on MS SQL, but i am not sure about My SQL.

jonesong · January 5, 2016, 5:50am

Yes, Khtan
but i just dissect part of the query. if you can look again the previous msg with the join table. u also asked the function of grouptodate. the grouptodate is a function to decode the date in the column of rd_group

if you look the query with the join table, how can i implement your advice to it? is not possible to have your query in my query right?

ops. typographical error in mysql. it should be msSql

jonesong · January 8, 2016, 1:34am

If i have another table joining them...

CREATE TABLE [dbo].[REGDTAIL2](

[rd_date]varchar NOT NULL DEFAULT (''),

[terminal] varchar NOT NULL DEFAULT (''),

[rd_type] char NOT NULL DEFAULT (''),
[rd_code] char NOT NULL DEFAULT (''),
[rd_ttrans] [smallmoney] NOT NULL DEFAULT ((0)),
[rd_tamount] [money] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

INSERT INTO [dbo].[REGDTAIL2]
(

[rd_date]

,[terminal]

,[rd_type]
,[rd_code]
,[rd_ttrans]
,[rd_tamount])
VALUES
('2015-12-12','A1','D','1',1.00,36.00),
('2015-12-12','A1','D','1',2.00,100.00),
('2015-12-12','A1','D','1',3.00,200.00),
('2015-12-12','A1','D','1',4.00,150.00),

('2015-12-13','A1','D','1',1.00,40.00),
('2015-12-13','A1','D','1',2.00,80.00),
('2015-12-13','A1','D','1',3.00,50.00)

CREATE TABLE [dbo].[REGHIST](

[date]varchar NOT NULL DEFAULT (''),

[terminal] varchar NOT NULL DEFAULT ('')

) ON [PRIMARY]

INSERT INTO [dbo].[REGHIST]
([date]
,[terminal])
VALUES
('2015-12-12','A1'),

('2015-12-13','A1'),

('2015-12-13','A2')

How can I use your advice if i have this query? The bold text is my old wrong condition. How will I use your advice if I have join table in this query? The table [REGHIST] has other columns I will used in the query but I didn't include it anymore to make it simple.

Select distinct
max(a.[terminal]) as [Terminal]

max(a.[date]) as [Date]

,isnull(max(Case when b.rd_type='D' and b.rd_code ='01' and b.rd_ttrans=
max(Case when b.rd_type='D' and b.rd_code ='01' then b.rd_ttrans end) then b.rd_tamount end),0) as [Line Disc%]

FROM [dbo].[REGHIST] a
left join dbo.regdtail b on a.date = b.rd_date
where a.date between '2015-12-12' and '2015-12-13'

and a.terminal = b.terminal

group by a.[date]

The result i want is

A1,2015-12-12,150.00

A1,2015-12-13,50.00