0

I want to create a query that Generate me a table that count all recent inputs by all users in every table from my DB.

If i insert since "2023-01-01". My query should show me all inputs done by all the users.

But is giving me the total amount off all inputs in each table. I don't know whats wrong with my query, any ideas?

WITH inputs_table AS (
    SELECT 'actions' as table_name, COUNT(*) as inputs FROM actions WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'activity', COUNT(*) FROM activity WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'calligraphy', COUNT(*) FROM calligraphy WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'drug_concentration', COUNT(*) FROM drug_concentration WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'drug_uptake', COUNT(*) FROM drug_uptake WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'pending_ld', COUNT(*) FROM pending_ld WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'phonation', COUNT(*) FROM phonation WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'pump_auto_drug', COUNT(*) FROM pump_auto_drug WHERE "created_at" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'symptom', COUNT(*) FROM symptom WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'tremor', COUNT(*) FROM tremor WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'tremor_watch_features', COUNT(*) FROM tremor_watch_features WHERE "date" >= '2022-12-30 00:00:00'
    UNION ALL
    SELECT 'vocal_word', COUNT(*) FROM vocal_word WHERE "date" >= '2022-12-30 00:00:00'
)
SELECT * FROM inputs_table
UNION ALL
SELECT 'TOTAL' as table_name, SUM(inputs) as inputs FROM inputs_table
UNION ALL
SELECT 'NEW_USERS' as table_name, COUNT(*) as inputs FROM user WHERE "date" >= '2023-01-01 00:00:00'

Example of my current output:

enter image description here

Bonus question ! :

is it possible to create a variable → WHERE "date" >= '2022-12-30 00:00:00'← like that is easier to update / change the code at once? Otherwise i need to change each and its not super performant.

1 Answer 1

1

The WITH and one UNION can be eliminated by using WITH ROLLUP to get the "Total" row.

(Trivial suggestion: '2023-01-01 00:00:00' can be simplified to '2023-01-01'.)

This may suffice for setting the date only once, then using the @variable many times:

SET @date = '2023-01-01';
SELECT ... WHERE date >= @date ...

What determines a "new user"? Perhaps one where users.initial_date >= ...? But I don't see that table being mentioned.

3
  • Hi amazing your option SET @date = '2023-01-01'; its work perfectly. I will use from now on to simplify my queries! Your hint is gold for me :). And for the problem that is only counting all inputs from all tables and not counting in the timestamp. Do you have any idea why ?
    – Rollo
    Jan 17, 2023 at 9:06
  • @Rollo - Are you saying that the TOTAL should not be the sum of the lines above it? In that case, your original UNION is needed, and my WITH ROLLUP will not work.
    – Rick James
    Jan 18, 2023 at 1:15
  • yes TOTAL row should be the count of numbers above in the table. But my questions was.. Why the output of my query is the total inputs in every list. for example "actions = 2744" is the total amount of inputs since the existence of the DB (4 years ago).. And i need that the count starts after "2023-01-01" to see the new inputs every month. or every since a Date is given
    – Rollo
    Jan 19, 2023 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.