CODEWITHSUNDEEP
sqlgrouping
Kevin
Kevin

Reputation: 57

Column Group and Count

Is it possible to create a query using the following table:

    YEAR    MAKE    MODEL
    2012    CHEVY   CAMARO
    2012    CHEVY   CORVETTE
    2013    CHEVY   CORVETTE
    2013    CHEVY   CAMARO
    2013    NISSAN  GT-R

that would output a result such as:

    CATEGORY    VALUE    TOTAL
    YEAR        2012     2
    YEAR        2013     3
    MAKE        CHEVY    4
    MAKE        NISSAN   1
    MODEL       CAMARO   2
    MODEL       CORVETTE 2
    MODEL       GT-R     1

Upvotes: 0

Views: 48

Answers (1)

realnumber3012
realnumber3012

Reputation: 1062

SELECT 'YEAR' AS category, CAST(year AS VARCHAR(4)), COUNT(*) AS total FROM YouTable
GROUP BY year
UNION ALL
SELECT 'MAKE' AS category, make, COUNT(*) FROM YouTable
GROUP BY make
UNION ALL
SELECT 'MODEL' AS category, model, COUNT(*) FROM YouTable
GROUP BY model

SQL standart query, if your RDBMS IS SQL SERVER version>=2005 OR Oracle version >=10g you can use PIVOT/UNPIVOT CLAUSE.

Upvotes: 1

Related Questions