Roll Up with multiple selects and join

Question

I have the following query:

select b.giorno , b.totali, b.unici, a.duplicati, round((a.duplicati/b.totali)*100) as percentuale
from
(
    select
        count(count) as duplicati,
        c.day as day
    from
        (
        select
            count(ip) as count,
            date(date) as day,
            ip
        from
            vas_updates
        group by
            ip
        having
            count > 1
        ) as c
    group by day
) as a
left join
(
    select
        count(id_update) as totali,
        count(distinct ip) as unici,
        date(date) as giorno
    from
        vas_updates
    group by
        date(date)
) as b on
    giorno = b.giorno AND
    giorno = day group by giorno

How do I get a final row that sums b.totali, b.unici, a.duplicati and percentuale individuality?

Which rdms are using. Some rdms's have special syntax for this. And the ones that do don't agree on syntax. You should specify the rdms for this reason — Conrad Frix, Feb 03 '16 at 15:20

score 0 · Answer 1 · answered Feb 03 '16 at 17:35

Try this,

SELECT  b.giorno ,
    SUM(b.totali)  AS totali,
    SUM(b.unici) AS unici ,
    SUM(a.duplicati) AS duplicati ,
    ROUND(SUM(( a.duplicati / b.totali ) * 100)) AS percentuale
 FROM    ( SELECT    COUNT(count) AS duplicati ,
                c.day AS day
      FROM      ( SELECT    COUNT(ip) AS count ,
                            date(date) AS day ,
                            ip
                  FROM      vas_updates
                  GROUP BY  ip
                  HAVING    count > 1
                ) AS c
      GROUP BY  day
    ) AS a
    LEFT JOIN ( SELECT  COUNT(id_update) AS totali ,
                        COUNT(DISTINCT ip) AS unici ,
                        date(date) AS giorno
                FROM    vas_updates
                GROUP BY date(date)
              ) AS b ON giorno = b.giorno
                        AND giorno = day
GROUP BY giorno

HI, it doesn't output anything different than mine. – Sandro Antonucci Feb 03 '16 at 17:45 — Sandro Antonucci, Feb 03 '16 at 17:45

Roll Up with multiple selects and join

1 Answers1