Question
Tristan Meziere · Nov 25, 2021

Cache SQL and GROUP BY clause

Hi members,

I have these data :

 

and need to exploit the first character of cot field (Library CDU classification) like this :

 

I get this result... it's approximatively what I want... but I need it in just one line !

With mySQL I get it with just GROUP BY rcddate

In Cache SQL I have to add {fn LEFT(cot,1)} to get the counts of each first character of cot... and seems {fn WEEK(rcddate)} doesn't make the job !

There is a trick ? or I make something wrong ?

kind regards.

Product version: Caché 2018.1
0
0 206
Discussion (3)1
Log in or sign up to continue

Forget about the week  and put your long query into a subselect

SELECT Sum(Dicos),Sum(Gen),Sum(Philo),Sum(Religion), Sum(SHS), ....
FROM (
.... your long query
)  

Hi Robert,

I confirmed I can make like this :

SELECT Semaine, SUM(Dicos) AS Dictionnaires, SUM(Gen) AS Généralités, SUM(Philo) AS Philosophie, SUM(Religion) AS Religion, SUM(SHS) AS SHS, SUM(Sciences) AS Sciences, SUM(Tech) AS Technique, SUM(Arts) AS Arts, SUM(Litt) AS Littérature, SUM(HisGeo) AS "Histoire - Géographie"
FROM (
SELECT 
{fn WEEK(rcddate)} AS "Semaine",
CASE {fn LEFT(cot,1)}
WHEN '(' THEN 1
END Dicos,
CASE {fn LEFT(cot,1)}
WHEN '0' THEN 1
END Gen,
CASE {fn LEFT(cot,1)}
WHEN '1' THEN 1
END Philo,
CASE {fn LEFT(cot,1)}
WHEN '2' THEN 1
END Religion,
CASE {fn LEFT(cot,1)}
WHEN '3' THEN 1
END SHS,
CASE {fn LEFT(cot,1)}
WHEN '5' THEN 1
END Sciences,
CASE {fn LEFT(cot,1)}
WHEN '6' THEN 1
END Tech,
CASE {fn LEFT(cot,1)}
WHEN '7' THEN 1
END Arts,
CASE {fn LEFT(cot,1)}
WHEN '8' THEN 1
END Litt,
CASE {fn LEFT(cot,1)}
WHEN '9' THEN 1
END HisGeo
FROM BCSCAN)
GROUP By Semaine

Sorry for the long time for the response... I've lost my tourist visa "Server Administrator" for two weeks at my job because security reasons and it's not completely resolved...

Many thanks

Hi,

It's another way ... but it smells the right way ! ;-)

Many thanks