SQL Query to get data from each column

Question

I have multiple columns in a table

One column may contain the name of the state (US), another column contains the speciality

Here is some sample data (there could be more than one C# in NY and may also exist in other states)

C# NY
C# NJ
C# WA
C# CA
C# NY
C# NY
Java NY
Java NJ
Java IL

Basically I want to get an output that has

C# 100 (sum of all states that have C#)
Java 85 (sum of all states that have Java)

and I would like to get

State  Total Speciality
NY     150   C# 
NY     100   C++

I am using SQLite3 as my DB

Paolo Falabella · Accepted Answer · 2012-02-26 21:09:06Z

1

SqlLite should have GROUP BY and COUNT. You may try:

SELECT Speciality, COUNT(State) AS Total
FROM YourTable 
GROUP BY Speciality

and

SELECT State, Speciality, COUNT(*)  AS Total
FROM YourTable 
GROUP BY State, Speciality

answered Feb 26, 2012 at 21:09

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1