If 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...
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 KilianJan 31 '12 at 19:42
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
@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
DECLARE @SimilarLanguageModifier VARCHAR(500) = (SELECT CASE WHEN LEFT(@LANG_A, LEN(@LANG_B)) = @LANG_B THEN /*your piece of where clause here*/ ELSE '' END)
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 + '%'
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
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?
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!
Here 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...