« first day (542 days earlier)      last day (102 days later) » 

2:20 AM
@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
2:24 AM
WHERE Body LIKE '%<h_>' + @LANGUAGE_A + '%'`
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
I know that bug
:D pls tell
If you don't specify the length of a varchar it defaults to varchar(1)
wait wat
2:26 AM
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)
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?
2:30 AM
@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
2:31 AM
....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:
A: Function to Calculate Median in Sql Server

Jeff AtwoodIf you're using SQL 2005 or better this is a nice, simple-ish median calculation for a single column in a table: SELECT ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDE...

2:33 AM
@quartata Yeah but I match explicitly NOT LANG_B
@Doorknob ik?
That's clever, and relatively simple given that there exists no Median() aggregate function. But how is it that no Median() function exists!? I'm a bit FLOOR()ed, frankly. — Charlie Kilian Jan 31 '12 at 19:42
@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.
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'
2:43 AM
@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
@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
2:47 AM
Just add another aggregate in your SELECT
So I have:
DECLARE @LANGA varchar(100) = ##LanguageA:string##
DECLARE @LANGB varchar(100) = ##LanguageB:string##

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 + '%'


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?
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
2:58 AM
Thank you
@Downgoat by all means let me know if you have more questions
3 hours later…
5:33 AM
I've an array [23, 24, 24, 25, 23]. If there is a match for every element in an array then
"SELECT * FROM tbl WHERE x IN (23, 24, 24, 25, 23)"
returns 3 rows. Any way I can fetch 5 rows as there are total 5 values to be matched?
5:44 AM
@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
6:00 AM
@Phrancis Thanks! I tried

SELECT t1.age FROM tbl AS t1 LEFT JOIN tbl AS t2 ON t1.age = t2.age WHERE t2.age IN (24, 24);

SELECT t1.age FROM tbl AS t1 LEFT JOIN tbl AS t2 ON t1.age = t2.age WHERE t2.age = 24 OR t2.age = 24;

Returned one row only.
@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
What database system are we talking about?
SQL Server, MySQL, etc.
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 *
6:18 AM
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
6:33 AM
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?
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?
6:35 AM
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)
6:44 AM
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!
Yes @Phrancis! Thanks a lot for helping me out here!
You're welcome :)
7 hours later…
2:18 PM
Q: improving this query?

user111114Here is my request : Select a, b, c, d, price *(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealTVA, price+price*(select p.value1 From Params as p where p.PAR_Field = 'Taxe' and p.PAR_KEY = 'TVA')/100 as RealPuTTc, Qte*(price+pri...


« first day (542 days earlier)      last day (102 days later) »