@Phrancis I'm trying both CONCAT and + to combine the strings and it's not working, ideas? :/

@Doorknob I swear I'm not crazy :P

@Downgoat Show me that part of your query

DECLARE @LANGUAGE_A varchar = '%<h_>' + ##LangA:string## + '%'
DECLARE @LANGUAGE_B varchar = '%<h_>' + ##LangB:string## + '%'

Oh I see

You can't have wildcards in regular strings

You need instead to concatenate them in your WHERE clause

that doens't work either

  AND Body     LIKE '%<h_>' + @LANGUAGE_A + '%'

that doesn't work like itmatches nothing

WHERE Body LIKE '%<h_>' + @LANGUAGE_A + '%'`

Oh

That's strange, I know I've used that before

maybe it's not supposed to be varchar in the DECLARE?

I've also tried nvarchar

OHHHHH

I know that bug

:D pls tell

If you don't specify the length of a varchar it defaults to varchar(1)

wait wat

Yes, correct

so how do i have a var llen var char

Pretty obscure bug, scratched my head for a while trying to figure it out

DECLARE @LANGUAGE_A varchar(N) where N is between 1 and 2000

100 should do nicely here

(it's not really a bug so much as a behavior that's not well-known about in SQL)

Average Votes between Languages SEDE

@Phrancis ah ok

If you wanted to get really fancy you could make your query into dynamic SQL so your column aliases would be the same as the input parameters

@Phrancis how?

wat

@Downgoat Average is not really meaningful here because of outliers. Maybe get the median as well

oh you mean construct a string and excute it?

@Downgoat correct

> NoLove4Chedda

;_; there is no sql median function

....do it manually?

@Quill oh, this doesn't handle <a> tags

write something to capture all the data

@Downgoat Pyth and Python are very very close. So there's that

nvm found easy thing:

SEDE is T-SQL

@quartata Yeah but I match explicitly NOT LANG_B

@Doorknob ik?

@Downgoat No, I'm just saying regarding the original conversation

Good night people

May you continue ever to parse html with regex.

@Doorknob though I doubt outliers affect golfing langs

@Doorknob SEDE is SQL Server 2015.

@Downgoat data.stackexchange.com/codegolf/query/510028/…

Or even better, 2016 R3.

stackstatus.net/post/143020795239/…

Now, beware, I generalized it so it works with any language but I had to remove the NOT LIKE clause of the first query. If Pyth vs. Python is a common enough query then you should make a special case for it

@Phrancis Can i do a LANG_A.startsWith(lang_b) kinda thing and have special behaviro?

Yes (ish)

The LEFT() and RIGHT() string functions work like that

(in your case specifically, LEFT(yourString, numChars) )

LEFT('Hello World', 4) returns 'Hell'

LEFT(@LANG_A, LEN(@LANG_B)) = @LANG_B

@Downgoat That might work fine. You could just make an additional variable that's an empty string if the condition is not true, otherwise it's a piece of where clause you just concat into the SQL string

ok

@Phrancis another question: can I make this a 2D table for output?

DECLARE @SimilarLanguageModifier VARCHAR(500) = (SELECT CASE WHEN LEFT(@LANG_A, LEN(@LANG_B)) = @LANG_B THEN /*your piece of where clause here*/ ELSE '' END)

@Downgoat A table is always 2D, can you clarify?

as in:

             Lang A  | Lang B
Mean    | 6.324 | 3.3241
Median  | 5       | 4

Yeah sure

         Lang A | Lang B
Mean    | 6.324 | 3.3241
Median  | 5     | 4

Just add another aggregate in your SELECT

So I have:

DECLARE @LANGA varchar(100) = ##LanguageA:string##
DECLARE @LANGB varchar(100) = ##LanguageB:string##

SELECT
CAST(SUM(Score) AS float) / CAST(COUNT(*) AS float) as [Mean Score],
COUNT(*) as [Total Posts]
FROM Posts
WHERE PostTypeId = 2
AND Body     LIKE '%<h_>' + @LANGA + '%'

UNION

SELECT
CAST(SUM(Score) AS float) / CAST(COUNT(*) AS float) as [Mean Score],
COUNT(*) as [Total Posts]
FROM Posts
WHERE PostTypeId = 2
AND Body     LIKE '%<h_>' + @LANGB + '%'

OK if I understand correctly you want to rotate the Mean and Median columns to be rows, right?

yes

Normally for bigger queries you can use fancy tricks, but for something simple like this I would just use your original query (like the one I modified for dynamic) to get the mean, then copy it and UNION it for median

You can just SELECT 'Mean' AS [Label], /* your Mean code here */ UNION SELECT 'Median' AS [Label], /* your median code here */

You can also use AS [] instead of AS [Label] if you don't want that column to have a name

It's a bit more work, but SQL doesn't really offer an "easy" way to do this, as it's not designed to make "presentational" tables, most people would copy the results into Excel and just use a pivot there

If there is a room owner or mod around, this SQL conversation could be moved to "SQL Helpline" chatroom

@Doorknob ^^

← 82 messages moved from The Nineteenth Byte

Thank you

@Downgoat by all means let me know if you have more questions

@m3huL The database is doing what it is supposed to do in that query. Sound like you would need to make a join

SELECT t1.YourCol FROM tbl AS t1 LEFT JOIN tbl AS t2 ON t1.YourCol = t2.YourCol WHERE t2.YourCol IS NOT NULL

Assuming YourCol is where those number are

@m3huL How many rows are you expecting?

I think I understand your problem. Does your tbl table have an identity field?

if the array is [24, 24] then two, if the array is [24, 24, 24] then three, if the array is [24, 24, 24, 24] then four...

yes the tbl has identity field but there would be only one row would be defined for value 24

Right

What database system are we talking about?

SQL Server, MySQL, etc.

mysql

OK, put the above query you posted as a subquery that includes the identity column, then select the value only from that

I think your "SELECT * FROM tbl WHERE x IN (23, 24, 24, 25, 23)" is probably not reflecting your actual table structure

If you had an identity column on tbl you would not get that problem with SELECT *

tbl has an ID auto_incr and values for each ages like VALUES (1, 24, 50), (2, 25, 65), (3, 26, 75)

@m3huL sqlfiddle.com/#!9/161c1/3/0 Is that similar to your setup?

The only way I can reproduce your issue is by using SELECT DISTINCT x FROM tbl

this matches my schema sqlfiddle.com/#!9/eeed8/1

@m3huL Okay, the query optimizer optimizes away redundant criteria in 23, 24, 24, 25, 23

Most likely, yes!

Is that the problem? As far as I know, this is intentional behavior

Yes! But in my case I need rows for every value match despite same. Currently I'm looping over the values and executing queries for every value!

So if you have WHERE x IN (23, 23, 23) you expect 3 rows?

Correct!

Why?

Well I don't expect that result from that query but If there is a way to achieve that by sql rather than executing multiple queries in loop.

And, do you expect the 1st row to match and the other 2 to be non-match?

Sorry, I didn't understand :(

x    match
--- ------
3    true
3    null
3    null

Like that^

It should match all, but I think it's the same

You can achieve that by SQL, by creating a temporary table with your x values, still not sure why you would want that though

If it matches once, it will match N times, as long as the source table isn't modified in the interim

the array values are user inputs. So there can be one or more same values

Do you expect the user to be smart enough to realize that searching for 23 and 23 may only yield one result? (because it's the same search criteria)

Yes! But each input represents an independent value that has to be taken into account for further calculation.

Okay, I'd suggest to do this on the application side then, the database shouldn't care about this stuff

You could always just run 1 query and then iterate the result set to display to the user

Number of inputs matters. I might try that as well!!

There's no point running queries in a loop if the criteria are the same

Anyways @m3huL hope this helped

most likely values would be like [23, 23, 24, 24, 25]. If i would run the query once then I have to calculate for same value inputs and then proceed further. Bit lengthy! Getting the results by looping over does the job as well and I don't need to write extra code!

You're welcome :)